By: Eli Leiba | Updated: 2017-01-19 | Comments | 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.
- 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.
- 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).
- 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.
- 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:
- On the result pane in SSMS
- In the current SQL Server Error Log
- 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.
- 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.
Last Updated: 2017-01-19
About the author
View all my tips