One common concern for SQL Server administrators is monitoring disk space usage. Running out of disk space can lead to serious performance issues and even data loss. In this article, we will discuss two different methods to detect if the disk space is running low on SQL Server.

Method 1: Using xp_fixeddrives

The first method involves using the stored procedure EXEC MASTER..xp_fixeddrives. This procedure returns two columns: drive name and free space in megabytes. To use this data in a query, you need to create a temporary table and insert the data from the stored procedure into it.

CREATE TABLE #DriveSpace (
    DriveName VARCHAR(50),
    FreeSpaceInMB INT
)

INSERT INTO #DriveSpace
EXEC MASTER..xp_fixeddrives

SELECT * FROM #DriveSpace

DROP TABLE #DriveSpace

This method provides a simple way to retrieve the available disk space, but it does not provide additional information about the database or file locations.

Method 2: Using sys.dm_os_volume_stats

The second method involves using the dynamic management view sys.dm_os_volume_stats. This view provides more detailed information about the disk space, including the logical name, drive letter, and free space in megabytes.

SELECT DISTINCT
    DB_NAME(dovs.database_id) AS DBName,
    dovs.logical_volume_name AS LogicalName,
    dovs.volume_mount_point AS Drive,
    CONVERT(INT, dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC

This query can be further modified to include the details of the actual file location:

SELECT DISTINCT
    DB_NAME(dovs.database_id) AS DBName,
    mf.physical_name AS PhysicalFileLocation,
    dovs.logical_volume_name AS LogicalName,
    dovs.volume_mount_point AS Drive,
    CONVERT(INT, dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC

By using this method, you can not only monitor the disk space but also identify which database is placed on which drive and the physical file location.

Both methods have their advantages, but method 2 provides more flexibility and detailed information. You can choose the method that best suits your needs and implement it on your production server.

Monitoring disk space usage is crucial for maintaining the performance and stability of your SQL Server. By regularly checking the disk space and taking appropriate actions, you can prevent potential issues and ensure the smooth operation of your database system.

Let us know which method you are using in your production server and if you have any other tips for monitoring disk space on SQL Server.