Alert for Free Disk space

  • balasach82

    SSCrazy Eights

    Points: 9647

    How to Create an Alert to monitor Disk Space(Free Space only)... so as to send mail if it falls below certain level

  • parthi-1705

    SSCrazy Eights

    Points: 9272

    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

  • homebrew01

    SSC Guru

    Points: 55126

    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 .

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply