SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

A very common question I often receive is how to detect if the disk space is running low on SQL Server.

There are two different ways to do the same. I personally prefer method 2 as that is very easy to use and I can use it creatively along with database name.

Method 1:

EXEC MASTER..xp_fixeddrives
GO

Above query will return us two columns, drive name and MB free. If we want to use this data in our query, we will have to create a temporary table and insert the data from this stored procedure into the temporary table and use it.

Method 2:

SELECT DISTINCT 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
GO

The above query will give us three columns: drive logical name, drive letter and free space in MB.

We can further modify above query to also include database name in the query as well.

SELECT DISTINCT DB_NAME(dovs.database_id) 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
GO

This will give us additional data about which database is placed on which drive.

If you see a database name multiple times, it is because your database has multiple files and they are on different drives. You can modify above query one more time to even include the details of actual file location.

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name 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
GO

The above query will now additionally include the physical file location as well.

As I mentioned earlier, I prefer method 2 as I can creatively use it as per the business need. Let me know which method are you using in your production server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

16 thoughts on “SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

  1. Hi Pinal,

    Could you please suggest some solutions for getting ‘TotalSpaceInMB’ column also with ‘FreeSpaceInMB’ column?

    Thanks in advance. :)

    Regards,
    Pawan

    • here it is:
      IF SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘productversion’)),1,2) < 10 –2000,2005
      BEGIN
      EXEC MASTER..xp_fixeddrives
      END
      ELSE –2008 and up.
      BEGIN

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

  2. Only Method 1 works in SQL Server 2008 (10.0.5500).

    The error you will get from all of the other methods described in this article is this one:
    Invalid object name ‘sys.dm_os_volume_stats’.

  3. Hi Pinal,

    This script is monitoring the fixed drives only could you please share if there is wa y to track the disk space of the mount points also, as in our environment the DB files are spread across anchor points as well as mount points.

  4. Hi Pinal,

    Is there a way where we can detect the memory, on web server we are allocating specific memory. So, is there a way where we can detect low memory on server.

    Thanks,
    Nilesh

  5. Follow-up: I run a vb script that accounts for disks that are mount points (if you use mount points, EXEC MASTER..xp_fixeddrives will not provide you with all the storage information you need). Below is what I use.
    strComputer = “.”
    Set objArgs = WScript.Arguments
    Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\CIMV2″)
    Set colItems = objWMIService.ExecQuery( “SELECT * FROM Win32_Volume WHERE Capacity>0″,,48)
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set OutputFile = fso.CreateTextFile(objArgs(0), True)
    ‘OutputFile.WriteLine “Caption,Capacity,FreeSpace,DriveLetter,MPDisk”
    For Each objItem in colItems
    OutputFile.WriteLine objItem.Caption & “,” & objItem.Capacity & “,” & objItem.FreeSpace & “,” &LEFT(objItem.Caption,1) & “,Y”
    ‘Wscript.Echo objItem.Caption & “,” & objItem.Capacity & “,” & LEFT(objItem.Caption,1) & “,Y”
    Next
    OutputFile.close

    Note, I write the file out as a comma-delimited file and use BCP to load a table to report on it later. NOTE: This is only good for Windows 2003 and up.

    Regards

  6. IF SUBSTRING(CONVERT(varchar,SERVERPROPERTY(‘productversion’)),1,2) < 10 –2000,2005
    BEGIN
    EXEC MASTER..xp_fixeddrives
    END
    ELSE –2008 and up.
    BEGIN

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

  7. To further clarify, the function “sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) ” is not valid for SQL Server 2008 base (I am running SP3 currently). I did some further in-depth research and I found I was not quite correct from my earlier statement. The function is only available for SQL Server 2008 R2 SP1 (NOT in SQL Server 2008 R2 RTM) NOR SQL Server 2008 (non-R2). Yes, there are a lot of us still running SQL 2008 non-R2.

    SO, the code resubmitted by Suran will not work for the SQL Server 2008 or SQL Server 2008 R2 (RTM) so you are stuck and still cannot look a the mount points.

  8. If you want to show the percentage of free space, just add this

    Convert(INT,((dovs.available_bytes/1048576.0/1024.0)/(dovs.total_bytes/1024.0/1024.0/1024.0)) * 100) AS PercentageFree

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s