Monitor in SQLServer Free Space with Available Disk

Question

For my SQLServer , how to Monitor free space in filegroups after considering the available disk size.

Answer 1

Hi MSTechie-7364,
Please refer to this query which might be helpful:

;WITH src AS  
(  
  SELECT FG          = fg.name,   
         FileID      = f.file_id,  
         LogicalName = f.name,  
         [Path]      = f.physical_name,   
         FileSizeMB  = f.size/128.0,   
         UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], 'SpaceUsed'))/128.0,   
         GrowthMB    = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128.0 END,  
         MaxSizeMB   = NULLIF(f.max_size, -1)/128.0,  
         DriveSizeMB = vs.total_bytes/1048576.0,  
         DriveFreeMB = vs.available_bytes/1048576.0  
  FROM sys.database_files AS f  
  INNER JOIN sys.filegroups AS fg  
        ON f.data_space_id = fg.data_space_id  
  CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs  
   
)  
SELECT [Filegroup] = FG, FileID, LogicalName, [Path],  
  FileSizeMB  = CONVERT(decimal(18,2), FileSizeMB),  
  FreeSpaceMB = CONVERT(decimal(18,2), FileSizeMB-UsedSpaceMB),  
  [%]         = CONVERT(decimal(5,2), 100.0*(FileSizeMB-UsedSpaceMB)/FileSizeMB),  
  GrowthMB    = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), '% warning!'),  
  MaxSizeMB   = CONVERT(decimal(18,2), MaxSizeMB),  
  DriveSizeMB = CONVERT(bigint, DriveSizeMB),  
  DriveFreeMB = CONVERT(bigint, DriveFreeMB),  
  [%]         = CONVERT(decimal(5,2), 100.0*(DriveFreeMB)/DriveSizeMB)  
FROM src  
ORDER BY FG, LogicalName;  

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I found this article useful: https://kevinholman.com/2016/04/03/how-sql-database-free-space-monitoring-works-in-the-sql-management-pack/