Sql-server – Is there to get space used in files for SQL Server database without negatively affecting IO

performancesql servert-sql

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.

Best Answer

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
Related Question