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?
I have also tried summing the
sys.allocation_units which seems to be another approach that SSMS uses - see here, but it's equally slow sadly.
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.