4

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:

  1. 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.
  2. 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.

6

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo.

First, create a #temp table to hold results:

CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname,
  physical_name sysname, FileSizeMb int, FileUsedMB int, FileFreeMB int,
  SpaceFreePerc decimal(18,2), SizeOnDiskMB int, volume_mount_point nvarchar(256), 
  logical_volume_name nvarchar(256), available_bytes bigint, total_bytes bigint);

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.

DECLARE @database_id int, @file_id int, @logical_name sysname, 
  @physical_name nvarchar(520), @size int, @sql nvarchar(max), @exec sysname;

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT database_id, [file_id], name, physical_name, size
  FROM sys.master_files;

SET @sql = N'SELECT 
     GETDATE() as dt ,@@SERVERNAME as srv, @logical_name, @physical_name    
     ,Round(@size * 8 / 1024, 2) as FileSizeMb
     , CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128 as FileUsedMB
     ,(@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) AS FileFreeMB
     ,Convert(decimal(18,2), (@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) / (@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 JOIN sys.dm_os_volume_stats(@database_id, @file_id) drv
 WHERE F.database_id = @database_id AND F.file_id = @file_id;';

OPEN c;
FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @exec = DB_NAME(@database_id) + N'.sys.sp_executesql ';    
  INSERT #x EXEC @exec @sql, N'@database_id int, @file_id int, 
    @logical_name sysname, @physical_name nvarchar(520), @size int',
    @database_id, @file_id, @logical_name, @physical_name, @size;

  FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;
END

SELECT * FROM #x;

CLOSE c; DEALLOCATE c;
  • Thanks, very nice solution. One question since I now realize that the size_on_disk_bytes value of dm_os_volume_stats is always the same as size from master_files - does it make any sense to have dm_os_volume_stats included here at all? – Magier May 4 '16 at 11:40
  • @Magier Probably not, I wasn't evaluating that part at all. – Aaron Bertrand May 4 '16 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.