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.

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)

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

Related Posts

37 Comments. Leave new

  • Howard Rothenburg
    September 1, 2016 2:07 am

    SELECT DB_NAME(dbid) AS DatabaseName,
    CAST((SUM(CAST(size AS BIGINT)) * 8) / (1024.0 * 1024.0) AS DECIMAL(10, 2)) AS DbSizeGb
    FROM sys.sysaltfiles
    GROUP BY DB_NAME(dbid);

    Reply
  • Thanks Pinal!!

    Reply
  • Is there a way to see if SQL Server is running a sql script out of disk space? because I’m having a trouble with low disk space, and then SQL Server does not respond, then when I restart the instance, the Database becomes in Recovery model.

    Reply
  • Arunkumar Babu
    March 1, 2017 10:38 am

    is it possible to find the same type of storage space on both primary and secondary replica of Alwayson

    Reply
  • how to change SSMS query results cache to another drive.?

    Reply
  • Could you please share a query to find local disk space (drive capacity and free space) along with ip address or machine name or database name in SQL Server 2005/2008 r2.

    Reply
    • Check this query I modified it to take only drives where SQL Data and log files reside.
      Works on SQL 2005/08 as well

      /*******************************************************/
      /* Enabling Ole Automation Procedures */
      sp_configure ‘show advanced options’, 1;
      GO
      RECONFIGURE;
      GO
      sp_configure ‘Ole Automation Procedures’, 1;
      GO
      RECONFIGURE;
      GO
      /*******************************************************/
      SET NOCOUNT ON
      DECLARE @hr int
      DECLARE @fso int
      DECLARE @drive char(1)
      DECLARE @odrive int
      DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
      CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace float(10),
      TotalSize float(10) ) INSERT #drives(drive,FreeSpace) EXEC
      master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
      ‘Scripting.FileSystemObject’,@fso OUT IF @hr 0 EXEC sp_OAGetErrorInfo
      @fso
      DECLARE dcur CURSOR LOCAL FAST_FORWARD
      FOR SELECT drive from #drives ORDER by drive
      OPEN dcur FETCH NEXT FROM dcur INTO @drive
      WHILE @@FETCH_STATUS=0
      BEGIN
      EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
      IF @hr 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
      sp_OAGetProperty
      @odrive,’TotalSize’, @TotalSize OUT IF @hr 0 EXEC sp_OAGetErrorInfo
      @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
      drive=@drive FETCH NEXT FROM dcur INTO @drive
      End
      Close dcur
      DEALLOCATE dcur
      EXEC @hr=sp_OADestroy @fso IF @hr 0 EXEC sp_OAGetErrorInfo @fso

      SELECT

      distinct substring(smf.physical_name, 1,1) as Drive, TotalSize as ‘Total(MB)’, FreeSpace as ‘Free(MB)’,

      ROUND(FreeSpace/TotalSize*100,2) as “% Free Space” , HOST_NAME() as HostName
      FROM sys.master_files smf
      cross apply #drives
      where substring(smf.physical_name, 1,1) = drive
      order by drive

      DROP TABLE #drives
      GO
      /*******************************************************/
      /* Disabling Ole Automation Procedures */
      sp_configure ‘show advanced options’, 1;
      GO
      RECONFIGURE;
      GO
      sp_configure ‘Ole Automation Procedures’, 0;
      GO
      RECONFIGURE;
      /*******************************************************/
      GO

      Reply
  • Hi Dave,

    Is there a way to find out Percentage of free space on Drive that contains no DBs,
    like a backup Drive or OS Drive?

    Thank you.

    Reply
    • SELECT distinct dovs.logical_volume_name, dovs.volume_mount_point, PercentFree = CAST(dovs.available_bytes as float)/dovs.total_bytes
      FROM sys.master_files mf
      CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

      Reply
  • Pinal, your articles are always valuable thank you. In this case I believe MS has a bug in sys.dm_os_volume_stats. It does not report mount points that only contain restoring database files. This creates significant reporting gaps on log shipped systems. Tested this in 2008 R2 SP3 as well as 2014 SP2. Any ideas on an alternate mount point reporting option that doesn’t include xp_cmdshell?

    Reply
  • Method #2 is really helpful, thank you, Pinal. I’ve never seen this query before, and I’ve been around a bit :)

    Reply

Leave a Reply

Menu