Alert for Free Disk space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Alert for Free Disk space

Alert for Free Disk space

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Friday, October 01, 2010 6:01 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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

parthi-1705

Posted Friday, October 01, 2010 7:18 AM

Mr 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

homebrew01

Posted Friday, October 01, 2010 8:37 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 2,555, Visits: 7,214

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 »

Permissions