Is there to get space used in files for SQL Server database without negatively affecting IO?

Question

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.

Answer 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

Answer 2

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,

Answer 3

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.