Disk Space Monitoring Using SQL Query

If you want to find how much space we have actually available in server, and want to know this using SQL query we can use sys.dm_os_volume_stats DMV.

This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server.

SELECT 

DISTINCT Logical_Volume_Name AS 'Logical Name', 

Volume_Mount_Point AS 'Drive',

CAST((Total_Bytes / 1024.0 / 1024) AS NUMERIC(18,1)) AS 'Total Space in MB', CAST((Available_Bytes / 1024.0 / 1024) AS NUMERIC(18,1)) AS 'Available Space in MB' ,

CAST((Total_Bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18,1)) AS 'Total Space in GB', CAST((Available_Bytes / 1024.0 / 1024 / 1024 ) AS NUMERIC(18,1)) AS 'Available Space in GB',

CAST((Available_Bytes / 1024.0 / 1024 / 1024)  / ( Total_Bytes / 1024.0 / 1024 / 1024 ) AS NUMERIC(18,3))*100 AS [Available Space in Percentage]

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

This can be use as a very handy query as you set up an alert like when [Available Space in Percentage] is goes beyond a certain level let say 10%, it will send a alert mail to DBA and he can take some actions before it too late.