Disk Space Monitoring

You may often need to check, if the disk space is running out of space on SQL Server. From SQL Server 2008 R2 SP1 introduces a DMF – Dynamic Management Function (sys.dm_os_volume_stats) that exposes several attributes of the physical disk drives that contain your database files. You can run the below script to get the disk utilization detail by SQL Server.

SQL Query

SELECT DISTINCT
              volume_mount_point [Disk Mount Point],
              file_system_type [File System Type],
              logical_volume_name as [Logical Drive Name],
              CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
              CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB], 
              CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
FROM sys.master_files
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql