Summary: Use sys.dm_os_volume_stats to audit physical drive space, mount points, and file system attributes directly from SQL Server for all database files.
Monitoring Database Volume Stats and Disk Space
Ever wonder about the nitty-gritty details of where your SQL Server databases and files live? There's a powerful dynamic management function (DMF) that spills the beans on the operating system volume where your data resides. It's incredibly useful for auditing physical disk attributes or getting a live view of available free space without leaving SQL Server Management Studio.
Volume Stats and Free Space Query
This script uses a CROSS APPLY to join your master files with their respective volume statistics, providing a consolidated view of every drive currently hosting a SQL Server data or log file.
-- Detailed breakdown of volume stats for all database files
SELECT DISTINCT
vs.volume_mount_point AS [Mount Point],
vs.file_system_type AS [File System],
vs.logical_volume_name AS [Volume Name],
CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)],
CONVERT(DECIMAL(18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
vs.supports_compression,
vs.is_compressed,
vs.supports_sparse_files,
vs.supports_alternate_streams
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY [Mount Point]
OPTION (RECOMPILE);
Why This Matters
Traditional methods like xp_fixeddrives only show drive letters. However, sys.dm_os_volume_stats is superior because:
- Mount Point Support: It correctly identifies volumes mounted as folders (Mount Points), which are common in enterprise SAN environments.
- Proactive Alerting: You can use the
Space Free %logic to build automated alerts before a disk fills up and takes your database offline. - Feature Awareness: It identifies if a volume supports modern features like Sparse Files (used by Database Snapshots and CheckDB) or Compression.
For more in-depth documentation on this function, refer to the official Microsoft documentation.
Is your disk space running low? Identifying the exact volume and its percentage of free space is the first step in effective capacity planning!
