Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alert for Free Disk space Expand / Collapse
Author
Message
Posted Friday, October 1, 2010 6:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
How to Create an Alert to monitor Disk Space(Free Space only)... so as to send mail if it falls below certain level
Post #996563
Posted Friday, October 1, 2010 7:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi
Try this out


CREATE PROCEDURE usp_DiskFreeSpaceAlert
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
-- This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)

DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)

/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives

SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'

IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')

open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace

WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #disk_free_space
GO

Thanks
Parthi



Thanks
Parthi
Post #996608
Posted Friday, October 1, 2010 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 2,832, Visits: 8,513
Just a note that xp_sendmail will get dropped at some point, so you could modify the code to use the current mail method of sp_send_dbmail .


Post #996703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse