SQL Server: Monitor Free Disk Space Safely

Monitor the volumes that contain SQL Server database files with sys.dm_os_volume_stats. It returns capacity information without enabling operating-system command execution.

Free space for database volumes

SELECT DISTINCT
       vs.volume_mount_point,
       vs.logical_volume_name,
       CAST(vs.total_bytes / 1073741824.0 AS decimal(18,2)) AS total_gb,
       CAST(vs.available_bytes / 1073741824.0 AS decimal(18,2)) AS free_gb,
       CAST(100.0 * vs.available_bytes /
            NULLIF(vs.total_bytes, 0) AS decimal(5,2)) AS free_percent
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point;

Depending on SQL Server version, the login needs VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE. Grant only the minimum permission required to a dedicated monitoring identity.

Alert on capacity and time-to-full

A fixed percentage alone is not enough. Ten percent free can be ample on a large volume and dangerous on a small one. Track:

• Free gigabytes and free percentage
• Daily and weekly growth rate
• Expected autogrowth events and largest possible operation
• Backup, restore, index maintenance, and tempdb space requirements
• Thin-provisioned or shared storage capacity outside the guest

Create warnings with enough lead time for a controlled response. Send them through the enterprise monitoring system or a SQL Server Agent job that records the query and notifies an approved operator.

When space is low

1. Identify which files and workloads are growing.
2. Confirm that an abnormal transaction, load, index operation, or backup failure is not responsible.
3. Add capacity or correct retention before shrinking files.
4. Validate autogrowth size and storage-level free space.

Routine shrinking usually creates fragmentation and does not solve ongoing growth. Also avoid enabling xp_cmdshell merely to run an operating-system free-space command; it creates a much larger security surface. The former one-line example has been replaced with a supported dynamic-management function and an actionable alerting approach.