locked
SQL Alerts "WMI event alert" for disk space monitor RRS feed

  • Question

  • Hi, how to create SQL 2005 alert using “WMI event alert” to monitor disk space in cluster environment? Cheers!

    Monday, September 8, 2008 11:02 PM

All replies

  • Don't know about a WMI solution, but maybe my solution can help. I have a stored proc that I run with an agent job every so often. You may need to customize this some to fit your environment. There are 3 places you need to edit the email address to send to. I know, I know... that should really be changed to a variable so it would only have to be changed in 1 place, but it works for me.

    USE [master]
    GO

    /****** Object:  StoredProcedure [dbo].[sp_DiskFreeSpaceAlert]    Script Date: 10/13/2008 10:26:50 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[sp_DiskFreeSpaceAlert]
        @DriveCBenchmark int = 2000,
        @LogDriveBenchmark int = 10000,
        @OtherDataDriveBenchmark int = 100000
    AS
    --Sends out an alert message when hard disk space is below a defined value.

    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

    --eliminate drive Q
    delete from #disk_free_space
    where DriveLetter = 'Q'

    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 is the OS location and low space could slow down performance of the server.'
    -- Send out email
    EXEC msdb..sp_send_dbmail
    @profile_name = 'default',
    @recipients = '
    DBA@KaushiKMakadia.com',
    @subject = @MailSubject,
    @body = @AlertMessage
    End

    --SAN Log drive and local drive E
    SET @DiskFreeSpace = null

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

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

    WHILE (@@FETCH_STATUS = 0)
    Begin
    if @DiskFreeSpace < @LogDriveBenchmark
    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 running out of space'
    -- Send out email
    EXEC msdb..sp_send_dbmail
    @profile_name = 'default',
    @recipients = '
    DBA@KaushiKMakadia.com',
    @subject = @MailSubject,
    @body = @AlertMessage
    End
    fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
    End

    close DriveSpace
    deallocate DriveSpace

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

    open DriveSpace2
    fetch next from DriveSpace2 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 running out of space'
    -- Send out email
    EXEC msdb..sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'DBA@
    KaushiKMakadia.com',
    @subject = @MailSubject,
    @body = @AlertMessage
    End
    fetch next from DriveSpace2 into @DriveLetter, @DiskFreeSpace
    End

    close DriveSpace2
    deallocate DriveSpace2
    DROP TABLE #disk_free_space

    GO



    Monday, October 13, 2008 2:32 PM
  • Hi,

     

    i found your script and am trying to understand certain things so i am alter it for my use..

     

        @DriveCBenchmark int = 2000, <-- 2000MB?
        @LogDriveBenchmark int = 10000,
        @OtherDataDriveBenchmark int = 10000 <--- 10GB?

     

    are these threholds in MB?

     

    Also will this script monitor drive g, h, i, j? etc?

    thanks,


    Thursday, April 15, 2010 8:46 PM
  • Hi,

     

    i found your script and am trying to understand certain things so i am alter it for my use..

     

        @DriveCBenchmark int = 2000, <-- 2000MB?
        @LogDriveBenchmark int = 10000,
        @OtherDataDriveBenchmark int = 10000 <--- 10GB?

     

    are these threholds in MB?

     

    Also will this script monitor drive g, h, i, j? etc?

    thanks,


    Thursday, April 15, 2010 8:47 PM
  • Hi,

     

    i found your script and am trying to understand certain things so i am alter it for my use..

     

        @DriveCBenchmark int = 2000, <-- 2000MB?
        @LogDriveBenchmark int = 10000,
        @OtherDataDriveBenchmark int = 10000 <--- 10GB?

     

    are these threholds in MB?

     

    Also will this script monitor drive g, h, i, j? etc?

    thanks,


    Thursday, April 15, 2010 8:47 PM