Saturday, 16 February 2013

Disk space alert per drive


When executing this script, it will create the stored procedure: usp_DiskSpaceAlert
Now you can create little jobs to check the available diskspace for 1 drive (so by adding more steps in the job, you can check all disks depending other parameters).
Variables:
- MinMBFree = triggeramount of free disk space
- Drive = diskdrive to check
- RCPT = emailaddresses of recipients
For example:
EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 3072, @Drive='C', @RCPT='someone@mail.com'
GO


USE [master]GO/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @Drive char(1), @RCPT VARCHAR(500) AS/*****************************************************    Object: dbo.usp_DiskSpaceAlert (DiskSpaceAlert per diskdrive)*    Dependent Objects: master.sys.xp_fixeddrives*    Version: 1.0*    Script Date: 3/10/2011*    Author: Sven Goossens*    Purpose: Validate sufficient disk space per drive*    Detailed Description: Validate sufficient disk space based on based on the @MinMBFree and @Drive parameters*        Mails when defined amount is reached to parameter @RCPT*    EXECUTE AS:*        EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 30000, @Drive='C', @RCPT='someone@mail.com'*    Updates:*     v1.0    - Drive will be checked and sends mail when diskspace is less then given amount****************************************************/SET NOCOUNT ON-- 1 - Declare variablesDECLARE @MBfree int-- 2 - Initialize variablesSET @MBfree = 0-- 3 - Create temp tablesCREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)-- 4 - Populate #tbl_xp_fixeddrivesINSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])EXEC master.sys.xp_fixeddrives-- 5 - Initialize the @MBfree valueSELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @Drive-- 6 - Determine if sufficient free space is availableIF @MBfree > @MinMBFreeBEGIN RETURNENDELSEBEGIN IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL    BEGIN        DECLARE @MSG VARCHAR(400)        SET @MSG = @Drive + ' drive has only ' + CONVERT(VARCHAR,@MBfree) --PUT THE VARS INTO A MSG            + 'MB (' +CONVERT(VARCHAR,@MBfree/1024)+ 'GB) left on ' + @@SERVERNAME + CHAR(13) + CHAR(10)        DECLARE @EMAIL VARCHAR(600)        SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail            @recipients = ''' + @RCPT + ''',            @body = ''' + @MSG + ''',            @subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @Drive + ' @ ' + @@SERVERNAME + ' !!'''        EXEC (@EMAIL)    ENDEND-- 7 - DROP TABLE #tbl_xp_fixeddrivesDROP TABLE #tbl_xp_fixeddrivesSET NOCOUNT OFFGO

No comments:

Post a Comment