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

SQL SERVER - Disk Space Monitoring - Detecting Low Disk Space on Server freemb1

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.

Solarwinds

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.

SQL SERVER - Disk Space Monitoring - Detecting Low Disk Space on Server freemb2

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.

SQL SERVER - Disk Space Monitoring - Detecting Low Disk Space on Server freemb3

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.

SQL SERVER - Disk Space Monitoring - Detecting Low Disk Space on Server freemb4

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 (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL – Download NuoDB and Qualify for FREE Amazon Gift Cards
Next Post
SQL SERVER – Weekly Series – Memory Lane – #040

Related Posts

36 Comments. Leave new

  • Might we worth noting the data collector here Dave. Trend analysis is often better than point in time analysis.

    Dave

    Reply
  • Hi Pinal,

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

    Thanks in advance. :)

    Regards,
    Pawan

    Reply
    • 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

      Reply
  • NotSoFastMyFriend
    August 2, 2013 6:11 pm

    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’.

    Reply
  • 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.

    Reply
  • Taufeeq Attar
    August 3, 2013 8:07 am

    yes…only first method works in SQL Server 2008(10.0.5512). for second method its showing error.Invalid object name ‘sys.dm_os_volume_stats’.

    Reply
  • Nilesh Macwan
    August 4, 2013 3:22 am

    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

    Reply
  • This errors on the reference to sys.dm_os_volume_stats because that is only valid for SQL Server 2012

    Reply
  • 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

    Reply
  • 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

    Reply
  • Suraj Deshpande
    August 6, 2013 7:37 pm

    Fantastic post Pinal, thanks for sharing.

    Reply
  • 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.

    Reply
  • Sorry!… it is available for SQL Server 2008 R2 SP1 and Higher… Cheers!

    Reply
  • 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

    Reply
  • thanks for sharing

    Reply
  • good scripts unfortunately method 2 does not show space on non database drive
    from method 1

    drive MB free
    C 41260
    D 37853
    E 175595
    H 409910
    I 446827
    K 4983
    L 7003
    M 189319
    P 21046
    S 5547
    T 5543
    X 375060
    method 2
    LogicalName Drive FreeSpaceInMB
    TempDB T:\ 5543
    SQL LOGS L:\ 7003
    SQL GET DATA H:\ 409910
    SQL EDW DATA I:\ 446827

    Reply
  • Love you posts, is there a way to add a volume to the script that doesnt contain an entry in sys.master_files? Specifically a backup drive?

    Reply
  • Script was useful but when i run on (Microsoft SQL Server 2005 – 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) ) SQL Server 2005 i was getting below error — Invalid object name ‘sys.dm_os_volume_stats’.
    Appreciate if you can help on this

    Reply
  • Hi Sir,

    how to check recently added space history of data/log files

    Thank you sir
    satya

    Reply
  • Hi,

    I used your script on an SQL 2008 R2 SP1 and get the error:
    Invalid Syntax near ‘.’.
    It is on the line:
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

    Do you know whats the issue?

    Thanks

    Reply
  • Interesting script. Wondering how you guys are using this and for what purpose. I mean, if I need to know the disk space, there is no need to run a SQL script to figure out what it is. I could just open “Computer” to see all drives and amount of space.

    Ideally the script should run daily, and store the value for each of the drives so it can look at it again when it runs the following day. Then when it runs the following day, it should determine the difference between free space between the two days. If you make the assumption that the amount of disk space could grow again the following day by the same amount, then based on that assumption, you could set the script to email you an alert X days before you run out of disk space and be pro-active in dealing with lack of disk space instead of reactive.

    Reply

Leave a Reply

Menu