Recently I was working on creating a custom dashboard to monitor the health of all the database servers. One thing I wanted to add to the dashboard was “Available Disk Space” on the data drives of SQL Server instances. And I thought I’m not going to get this information in any system table or DMV and I’ll need to get the disk space information via some window commands or something. Then I came across this dynamic management function sys.dm_os_volume_stats , which provided the exact information I was looking for.
sys.dm_os_volume_stats:
Returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. Use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory.Here is the script that gives you the useful information about the total and available size of attached data volumes on SQL Server instance.
--------------------------------------------------------------------------------------------------------
SELECT DISTINCT d.logical_volume_name AS LogicalName
,d.volume_mount_point AS DriveName
,CONVERT(NUMERIC(32,2),d.available_bytes *1.00/(1048576.0 *1024 ))AS FreeSpaceInGB
,CONVERT(NUMERIC(32,2),d.Total_Bytes *1.00/(1048576.0 *1024 )) AS TotalSizeInGB
FROM sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID) d
ORDER BY FreeSpaceInGB
--------------------------------------------------------------------------------------------------------
This script is very useful in monitoring the data volumes, tracking the growth of the data, detecting the low disk space on the data drive of SQL Server etc. I have implemented the low disk space alert on my Servers using this script.
