3

I need to get the space used within each database file across large numbers (1000+) of databases on a regular basis without hitting disk IO too hard.

I thought I could simple call the FILEPROPERTY(@file, 'SpaceUsed') function or sp_spaceused - however, the disk IO spikes to near 100% for quite a few seconds when I loop over a large number of databases calling either of these functions.

I suppose that SQL Server must interally have some idea of how much space is used (or left) in its files so that it can auto-grow. I wonder if there is some way to get hold of these values (even if they are less accurate than FILEPROPERTY) without causing such as massive hit on IO?

Thanks :)

Update

I have also tried summing the total_pages from sys.allocation_units which seems to be another approach that SSMS uses - see here, but it's equally slow sadly.

Update 2

I realise I can also use DBCC showfilestats (and multiply the UsedExtents by 64), to get the used size of the database file and DBCC SQLPERF (LOGSPACE) to get the used size of the log - initial testing shows these to be better in terms of IO hit.

  • What version of SQL server you are using? – SqlWorldWide Jul 26 '17 at 11:33
  • See this and this. – SqlWorldWide Jul 26 '17 at 11:50
  • Thanks @SqlWorldWide all versions from SQL2008 - SQL2016. – Mark Jul 26 '17 at 13:38
  • @SqlWorldWide thanks for the links - however they all use either FILEPROPERTY (which is slow) or sys.allocation_units, which also is slow, it turns out. Is there a faster and more "rough and ready" version? – Mark Jul 26 '17 at 13:39
  • 1
    I'd reckon that if a better version would exist, that Microsoft would have implemented it into their 'Reports' - 'Standard Report' - 'Disk Usage'. You could always trace what they do with SQL Server Profiler and see if you can tweak it a bit. – hot2use Jul 26 '17 at 15:18
1
SELECT
 DB.name,
 MF.physical_name,
 SUM(MF.size * 8 / 1024.0 /1024.0) AS FileSizeGB
FROM
 sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- exclude snapshots
GROUP BY DB.name, MF.file_id, MF.physical_name
ORDER BY FileSizeGB DESC;

or

SELECT 
 DB_Name(vfs.DbId) DBname,
 MF.physical_name,
 vfs.DBid,vfs.FileID,
 vfs.BytesOnDisk
FROM ::fn_virtualfilestats(null,null) vfs
JOIN sys.master_files MF ON vfs.DBid = MF.database_id
 AND vfs.FileId = MF.file_id
  • SELECT DB_Name(vfs.DbId) DBname,vfs.DBid,vfs.FileID,vfs.BytesOnDisk FROM ::fn_virtualfilestats(null,null) vfs – Igor Jul 27 '17 at 13:56
  • you can edit your own post to add the latter bit. Please give some explanation, too. – dezso Jul 27 '17 at 14:02
  • @Igor I already know how to get the file size, and, yes, that's quick and IO friendly - this question is about getting the "Spaced Used" within the file, which is what I really need – Mark Jul 28 '17 at 10:27
  • if you use one .LDF file per database (draft version): SELECT db.[name] AS [Database Name], lu.cntr_value AS [Log Used (KB)] FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db.name = lu.instance_name WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'; – Igor Jul 28 '17 at 12:33
1

A much better way and more scalable (when you have to do it on many servers) is to use Get-DbaDatabaseFreespace command from dbatools

This function returns database file space information for a SQL Instance or group of SQL Instances. Information is based on a query against sys.database_files and the FILEPROPERTY function to query and return information. The function can accept a single instance or multiple instances. By default, only user dbs will be shown, but using the IncludeSystemDBs switch will include system databases,

  • 1
    Thanks, but the fact that this is using "FILEPROPERTY" means that it is not going to be much better for what I'm asking for (which is get space used within the file in a way that's not so IO intensive as the already tried fileproperty or summing the sys.allocation_units) – Mark Jul 28 '17 at 10:32
  • Fileproperty is not that io intensive. I use it in all my env. for 500+ servers including mission critical 5+ TB dbs. – Kin Shah Jul 28 '17 at 12:08
  • That's good to hear. However on my 1000+ database system I'm seeing significant IO hit - which I guess is why they made it a function to call rather than making it a "cheap" property in the DMV. – Mark Jul 29 '17 at 14:03
0

however, the disk IO spikes to near 100% for quite a few seconds when I loop over a large number of databases

Would it be acceptable to reduce the effective resource hit by smearing it out over a longer time? If you are cursoring around a large number is databases on one instance you could introduce WAITFOR DELAY '00:00:01' to pause one second between each iteration of the loop. Over 1,000 databases this will add about 17 minutes to your loop, which should not be a problem if it is a daily operation. IIRC WAITFOR accepts fractions of seconds, so you could tune this down with WAITFOR DELAY '00:00:00.5' or similar.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.