Alert When Not Enough Free Disk Space

,

--Implement an Alert Which Trigger when Specified Disks
--free space exceeds a specified alert level

--Steps to implement:
--1) Define a Custom Error Message with messsage text (The ---drive free space is bellow alert level. Details: %s)

--2) Define an Alert linked to CEM defined at step 1

--3) Implement a Job which execute the following step in a recurring way...

--Step
--here you specify  the drives you are intersted in
--and the alert level...The unit measure for alert level is MB
DECLARE @Doc varchar (1000)

SET @Doc = '
    
   
   
    '

EXEC dbo.usp_AlertOnDriveFreeSpaceLevel @Doc

CREATE   PROCEDURE usp_AlertOnDriveFreeSpaceLevel (@doc varchar (1000)) AS
SET NOCOUNT ON
DECLARE @hdoc int
--set a temporary table which hold levels
DECLARE @AlertSet TABLE (Drive varchar (2) , AlertLevel int)

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

INSERT @AlertSet 
SELECT *
FROM OPENXML (@hdoc,'root/drive',2)
WITH (Drive varchar(2) '@Label',
	AlertLevel int '@Alert')

EXEC sp_xml_removedocument @hdoc


CREATE TABLE #DriveFreeSpace (Drive varchar (2), FreeSpace int)

INSERT #DriveFreeSpace 
	EXEC master..xp_fixeddrives

---get the set of drives with freespace bellow AlertLevel
DECLARE @StrMessage varchar (500)

SET @StrMessage = ''

SELECT @StrMessage = @StrMessage + ' Drive ' + D.Drive +
			' With Free Space ' +  CAST (D.FreeSpace AS varchar)+
			' Has Exceeded Alert Level ' +  CAST (A.AlertLevel AS varchar) + '.'
			FROM @AlertSet A
			JOIN #DriveFreeSpace D
			ON 	D.Drive=A.Drive
			AND A.AlertLevel >= D.FreeSpace

IF @StrMessage <> '' 
	RAISERROR (51000 , 16,1, @StrMessage)	
	WITH LOG

DROP TABLE #DriveFreeSpace 

SET NOCOUNT OFF

Rate

Share

Share

Rate