Ever need to quickly check the available space on your SQL Server drives? Here are a couple of handy SQL queries that can help you out. We'll explore using a dynamic management view and a system stored procedure.
Method 1: Using `sys.dm_os_enumerate_fixed_drives`
This dynamic management view provides detailed information about fixed drives, including their path, type, and free space. It's a robust method for obtaining precise data.
SELECT fixed_drive_path
,drive_type_desc
,CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
OPTION (RECOMPILE);
Method 2: Using `xp_fixeddrives`
For a simpler and quicker overview, the `xp_fixeddrives` extended stored procedure is a great option. It returns a summary of free space on fixed drives.
xp_fixeddrives
You can find more in-depth information about sys.dm_os_enumerate_fixed_drives on the Microsoft documentation.
Further Reading on SQL Server Space Management
If you're interested in learning more about managing space in SQL Server, check out these related posts:
