Summary: Quickly audit available disk space on your SQL Server host using sys.dm_os_enumerate_fixed_drives and xp_fixeddrives to prevent disk fullness issues.
How to Check Available Disk Space in SQL Server
Ever need to quickly check the available space on your SQL Server drives without logging into the OS? Here are two handy methods to query disk space directly from a query window. We'll explore using a modern Dynamic Management View (DMV) and a classic system stored procedure.
Method 1: Using sys.dm_os_enumerate_fixed_drives
This DMV is the preferred modern method. It provides detailed information about fixed drives, including their path, type, and exact free space in bytes. It is highly precise and easy to format into Gigabytes (GB).
-- Querying disk space using a DMV
SELECT
fixed_drive_path AS [Drive],
drive_type_desc AS [Type],
CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
ORDER BY [Available Space (GB)] ASC
OPTION (RECOMPILE);
Method 2: Using xp_fixeddrives
For a simpler and quicker overview, the xp_fixeddrives extended stored procedure is a classic standby. While it provides less detail than the DMV, it is excellent for a fast snapshot of MB available on each drive.
-- Simple summary of drive free space (MB)
EXEC xp_fixeddrives;
For more technical details on how the engine enumerates these paths, refer to the official Microsoft documentation.
Is your TempDB growing too fast? Monitoring your disk space is the first step in maintaining a healthy SQL Server environment!
