Simple free drive space alert for SQL Server databases

By: Eli Leiba   |   Updated: 2017-01-19   |   Comments   |   Related: > Monitoring

Problem

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.

Solution

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]
GO

CREATE PROC dbo.usp_AlertOnDBSpace (@minFreeDrvPctAllowed DECIMAL(5, 1) = 30.0)
AS
BEGIN
 SET NOCOUNT ON

 DECLARE @localDrivesSpaceInfo TABLE (
  idx INT IDENTITY
  ,DriveLetter CHAR(1)
  ,FreeSpaceGB FLOAT
  ,TotSizeGB FLOAT
  ,pctFree AS round(100.0 * FreeSpaceGB / TotSizeGB, 1)
  );
 DECLARE @infoFromPS TABLE (data VARCHAR(200));
 DECLARE @i INT = 0
 DECLARE @tblen INT
 DECLARE @currDrv CHAR(1)
 DECLARE @currDrvPct DECIMAL(5, 1)
 DECLARE @freeGB FLOAT
 DECLARE @totGB FLOAT
 DECLARE @warnMsg VARCHAR(128)

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

 DELETE
 FROM @infoFromPS
 WHERE data IS NULL
  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
 BEGIN
  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)
  BEGIN
   SET @warnMsg = CONCAT (
     'Warning! - Drive "'
     ,@currDrv
     ,'" has '
     ,rtrim(ltrim(Str(@currDrvPct)))
     ,'% of disk space left!'
     ,' ('
     ,rtrim(ltrim(Str(@freeGB)))
     ,' GB free out of '
     ,rtrim(ltrim(Str(@totGB)))
     ,' GB ) '
     )

   SELECT @warnMsg AS msg

   -- log it in viewer and in srv error logs 
   EXEC xp_logevent 60000
    ,@warnMsg
    ,warning
  END

  SET @i = @i + 1
 END
END
GO

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
go
exec dbo.usp_AlertOnDBSpace
go

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) 

Notes

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.

About the author

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