Disk space alert per drive

  • Comments posted to this topic are about the item Disk space alert per drive[/url]

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 10/03/2011 15:50:05 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DiskSpaceAlert]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_DiskSpaceAlert]

    GO

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 10/03/2011 15:50:05 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE 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 variables

    DECLARE @MBfree int

    -- 2 - Initialize variables

    SET @MBfree = 0

    -- 3 - Create temp tables

    CREATE TABLE #tbl_xp_fixeddrives

    (Drive varchar(2) NOT NULL,

    [MB free] int NOT NULL)

    -- 4 - Populate #tbl_xp_fixeddrives

    INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])

    EXEC master.sys.xp_fixeddrives

    -- 5 - Initialize the @MBfree value

    SELECT @MBfree = [MB free]

    FROM #tbl_xp_fixeddrives

    WHERE Drive = @Drive

    -- 6 - Determine if sufficient fre space is available

    IF @MBfree > @MinMBFree

    BEGIN

    RETURN

    END

    ELSE

    BEGIN

    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(800)

    SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail

    @recipients = ''' + @RCPT + ''',

    @body = ''' + @MSG + ''',

    @subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @Drive + ' @ ' + @@SERVERNAME + ' !!'''

    EXEC (@EMAIL)

    END

    END

    -- 7 - DROP TABLE #tbl_xp_fixeddrives

    DROP TABLE #tbl_xp_fixeddrives

    SET NOCOUNT OFF

    GO

  • Difficulty to see the code of SP

  • Yes I know, I've tried to correct it, but it didn't helped.

    I even started an topic to help me, but no solutions atm ... 🙁

  • Thanks for the script, hoewever it's hard to read. Perhaps include the script as an attachment to your article instead of putting it in a code block 😉

  • I've modified the first post to make it more readable.

    Sorry for the hard reading article, but I did my best to post it good!

    Hope this edit will help you all....

  • Thank you. I appreciate you posting your script. Might I suggest a link on configuring db mail, as this would be a prerequisite.

    http://www.kodyaz.com/articles/sql2005-database-mail.aspx

  • sp_send_dbmail needs a profile to send a message from. I added it as a param. If null then grab a profile from a system table. Also removed the duplicate DROP TABLE statement.

    PS - Got those non-printable chars out of there that seem to appear in a lot of code posts. I'm told it has something to do with the editor used.

    Ken

    USE [master]

    GO

    /****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 12/08/2011 10:35:42 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @Drive char(1), @RCPT VARCHAR(500), @MailProfile varchar(400) = null

    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 variables

    DECLARE @MBfree int

    -- 2 - Initialize variables

    SET @MBfree = 0

    -- 3 - Create temp tables

    CREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)

    -- 4 - Populate #tbl_xp_fixeddrives

    INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])

    EXEC master.sys.xp_fixeddrives

    -- 5 - Initialize the @MBfree value

    SELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @Drive

    -- 6 - Determine if sufficient free space is available

    IF @MBfree > @MinMBFree

    BEGIN

    RETURN

    END

    ELSE

    BEGIN

    IF CHARINDEX('@',@RCPT) > 0

    --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL????

    BEGIN

    DECLARE @MSG VARCHAR(400)

    --Get a random profile from database mail if one is not passed in

    IF @MailProfile IS NULL SELECT @MailProfile = [NAME] FROM msdb..sysmail_profile

    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

    @profile_name = ''' + @MailProfile + ''',

    @recipients = ''' + @RCPT + ''',

    @body = ''' + @MSG + ''',

    @subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @Drive + ' @ ' + @@SERVERNAME + ' !!'''

    EXEC (@EMAIL)

    END

    END

    -- 7 -

    DROP TABLE #tbl_xp_fixeddrives

    SET NOCOUNT OFF

  • Thx Ken, I've used the default mail profile.

    If it's not set, the errormessage will say enough to create one.

    And there was no double drop table statement ... the first one was only a comment for documentation purpose in the stored procedure... 🙂

    Thanks for your reply, the extra addition to add the selection of the mail profile is very usefull!

  • Oh, true about the DROP TABLE statement. I accidently uncommented out the comment when reformatting :laugh:

    Ken

  • great work this.

  • Washout (2/13/2014)


    great work this.

    Thx 🙂

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

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