Check all drive space on the server

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:

• Get TempDB Version Store Space Usage by Database
• Log File Used Space

Get any memory dumps from SQL Server

Understanding the status of memory dumps in your SQL Server instance is crucial for troubleshooting and performance analysis. This post provides a simple method to quickly get details about any memory dumps, including their location, when they occurred, and their size.

Retrieve SQL Server Memory Dump Information

You can use the following SQL query to access data from the sys.dm_server_memory_dumps dynamic management view. This view provides valuable insights into recent memory dump events.

SELECT
    [filename],
    creation_time,
    size_in_bytes / 1048576.0 AS [Size (MB)]
FROM
    sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY
    creation_time DESC
OPTION (RECOMPILE);

This query will return the **filename** of the dump, its **creation time**, and its **size in megabytes**. The results are ordered by creation time, showing the most recent dumps first.

For more in-depth information about the sys.dm_server_memory_dumps view, you can refer to the official Microsoft documentation: sys.dm_server_memory_dumps on Microsoft Docs

Related SQL Server Memory Management Resources

• Get Memory Clerk Usage
• Get In-Memory OLTP Index Usage
• Check Memory Pressure on the Server
• Memory Grants Pending Value
• Resource Governor Resource Pool Information