Simple free drive space alert for SQL Server databases

By:   |   Comments   |   Related: > Monitoring


You want a simple and fast alerting tool to notify you about drives running out of disk space. You have several data files and log files located on one or more disk drives and you want to get an alert message in your Windows application log whenever one or more of these drives goes below X percent of free space.  In this tip we look at a simple script that you can use to alert you when disk space is getting low.


I have coded a stored procedure called dbo.usp_AlertOnDBSpace. The procedure uses xp_cmdshell to call a PowerShell script that uses a WMI call in order to get the drives space information. This was taken from this previous tip by Jugal Shah.  There are several other ways this could be done, but I wanted to build upon something that already existed.  Unfortunately I could not use xp_fixeddrives, because this only shows how much free space is left.

Here are the steps of the stored procedure.

  1. The procedure gets the space information from the PowerShell Get-WMIObject Win32_LogicalDisk command filtered on local hard drives. It gets the drive letter, the number of free bytes and the number of total bytes for each disk.
  2. The procedure enters all this data, parses and converts to GB (divided by 1024^3) into a table variable. The free disk percentage is a computed column calculated as round (100.0 * FreeSpaceGB / TotSizeGB, 1).
  3. The procedure loops over all the rows in the table and checks if the drive is used for storing SQL Server data. To do so, it checks the sys.master_files system table physical name column for the drives that are used.
  4. If the procedure finds the drive is used for storing SQL Server data or log files and the drive free space percentage is below the @minFreeDrvPctAllowed parameter, it prints an alert message and logs it in the current SQL Server Error Log and in the Windows Event Log by using the xp_logevent extended stored procedure.

Here is the T-SQL for the stored procedure:

USE [master]

CREATE PROC dbo.usp_AlertOnDBSpace (@minFreeDrvPctAllowed DECIMAL(5, 1) = 30.0)

 DECLARE @localDrivesSpaceInfo TABLE (
  ,DriveLetter CHAR(1)
  ,FreeSpaceGB FLOAT
  ,TotSizeGB FLOAT
  ,pctFree AS round(100.0 * FreeSpaceGB / TotSizeGB, 1)
 DECLARE @infoFromPS TABLE (data VARCHAR(200));
 DECLARE @currDrv CHAR(1)
 DECLARE @currDrvPct DECIMAL(5, 1)
 DECLARE @warnMsg VARCHAR(128)

 EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID,FreeSpace,Size"';

 FROM @infoFromPS
  OR data LIKE '%DeviceID%'
  OR data LIKE '%----%'
  OR len(rtrim(ltrim(data))) < 3;

 INSERT INTO @localDrivesSpaceInfo
 SELECT LEFT(data, 1) AS driveLetter
  ,ltrim(rtrim(substring(data, 3, len(data) - 2 - charIndex(' ', REVERSE(data), 1)))) AS FreeSpaceGB
  ,right(data, charIndex(' ', REVERSE(data), 1) - 1) AS TotSizeGB
 FROM @infoFromPS

 SELECT @tblen = count(*)
 FROM @localDrivesSpaceInfo

 SET @i = @i + 1

 WHILE @i <= @tblen
  SELECT @currDrv = DriveLetter
   ,@currDrvPct = pctFree
   ,@freeGB = ROUND(FreeSpaceGB / POWER(1024, 3), 1)
   ,@totGB = ROUND(TotSizeGB / POWER(1024, 3), 1)
  FROM @localDrivesSpaceInfo
  WHERE @i = idx;

  IF (
    @currDrv IN (
     SELECT DISTINCT left(upper(rtrim(ltrim(t.physical_name))), 1) AS DB_Drive
     FROM sys.master_files t
   AND (@currDrvPct < @minFreeDrvPctAllowed)
   SET @warnMsg = CONCAT (
     'Warning! - Drive "'
     ,'" has '
     ,'% of disk space left!'
     ,' ('
     ,' GB free out of '
     ,' GB ) '

   SELECT @warnMsg AS msg

   -- log it in viewer and in srv error logs 
   EXEC xp_logevent 60000

  SET @i = @i + 1

The procedure can be used by passing in a percentage value or it will use the default 30% that I have coded in the stored procedure.

For my sample run, I have one drive (C) on my local server with ~25% of free disk space.

Here is an example how to use the procedure with the default 30% check.

use master
exec dbo.usp_AlertOnDBSpace

After executing the statements, I get the following message in three places:

  1. On the result pane in SSMS
  2. In the current SQL Server Error Log
  3. In Windows Event Viewer in the Windows Application Log the message is a Warning.
Warning! - Drive "C" has 25% of disk space left! (75 GB free out of 298 GB) 


This was tested with SQL Server 2012 and 2014 Developer editions.

Next Steps
  • Implement a daily T-SQL job that runs the procedure with a @MinimalFreeGB parameter value of your choice that meets your system demands and your space requirements.
  • Modify the code to also have it send an email alert when there is an issue.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms