Custom Alert - Disk Free Space (Percent)

Description

This alert will monitor each drive being used by SQL Server. It first gets the free space in MB by running xp_fixeddrives and then gets total disk space from perfmon / WMI. It will return each pair of drive letter and percent free space to the alert so appropriate threhsolds can be tested.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: Disk Percent Free Space

Execution Interval: 1 hour

Notification Text: The following disk drives are running low on disk space.

SQL Script:

SET NOCOUNT ON

DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20);

-- Create a temp table for our working data. Load free space info into it first for each drive

IF OBJECT_ID('tempdb..#drives') IS NOT NULL

   DROP TABLE #drives

CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,TotalSize int NULL)

INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives

-- Open up a connection to perfmon so we can get total space for a drive

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

-- walk thru each drive and get the total space from perfmon

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=CONVERT(numeric,@TotalSize)/1024/1024 WHERE drive=@drive

   FETCH NEXT FROM dcur INTO @drive

END

-- cleanup after ourselves

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

-- return the results to the Ignite alert

-- For Ignite alerts, it expects this query to return the drive letter and then one more column

-- If you want to alert on Free MB, leave the drive and FreeSpace columns uncommented

-- If you want to alert on Free %, leave the drive and Free(%) columns uncommented - this is the default

SELECT drive,

--   TotalSize as 'Total(MB)',

--   FreeSpace as 'Free(MB)',

   100.0 * FreeSpace / TotalSize 'Free(%)'

FROM #drives

ORDER BY drive

DROP TABLE #drives

Execute Against: Monitored Instance

Units: % Free

High Threshold: Min 0, Max 5

Medium Threshold: Min 5, Max 10

  • I would not recommend this alert be used in DPA any longer. DPA in much older versions had some metrics that would also us OLE automation procedures as you can see this is doing with the sp_OAMethod. The issue with this is that it must be enabled on the SQL server you are monitoring. 

    see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option?view=sql-server-ver15

    In addition to it having to be enabled. The sessions from these some times do not return data or orphan on the server. DPA by default has a max for the sessions it will us to connect to any monitored server at 13. 

    Often if enough sessions hang or do not return data from these OLE automation calls the max sessions is reached and the DPA monitoring stops. 

    We get errors in the logs such as this one. 

     Monitor failed to start due to [An SQLException was provoked by the following failure: java.lang.Exception: max allowed monitored database sessions exceeded