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.