I need a query that puts together the following results:
- one row per database file across all databases
- additional columns with information about the locigal drive the file is on
- information about the size and the usage of the file
So far I have put together the following query:
SELECT
GETDATE() as dt
,@@SERVERNAME as srv
,F.name
,F.physical_name
,Round(F.size * 8 / 1024, 2) as FileSizeMb
, CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128 as FileUsedMB
,(F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) AS FileFreeMB
,Convert(decimal(18,2), (F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) / (F.size * 8 / 1024.1) * 100) as SpaceFreePerc
,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes
FROM sys.master_files F
inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id
CROSS APPLY sys.dm_os_volume_stats(F.database_id, F.FILE_ID) drv
Now I face two problems here:
- The FILEPROPERTY SpaceUsed is only available for the database in current context – so I need a solution to collect all Fileproperties from all dbs / files.
- CROSS APPLY dm_os_volume_stats does not work for dbs with compatibility level 80, but I have some of those in place.
Is there a way to reach this goal with the given conditions? The solution does not have to be a one-statement query, since it will anyways be part of a stored procedure in the end.
Best Answer
To avoid
CROSS APPLY
the simplest way I can think of is to callsys.dm_os_volume_stats
with explicit parameters fordatabase_id
andfile_id
. This means executing a single-row result for every db/file combo.First, create a #temp table to hold results:
Now, some variables and a cursor. The only tricky part here really is the
@exec
variable, which allows each iteration of the dynamic SQL to be executed in the right database context.