none
monitor disk space using alerts

    Question

  • I need to receive an email when a disk space on a given drive (which has SQL DB files) falls below 3 GB:

    - I need to use alerts (I don't want to use a SQL job to run some SP every x mins).

    - SQL Server performance condition alert cannot be used, because SQL cannot access non-SQL performance counters

    - if you use WMI event alert, please provide a query

    - registry cannot be modified (I know what you were thinking)

    Seems like a rather common DBA need, and I can't find a solution.  Thanks in advance.

    Monday, July 11, 2011 1:21 PM

Answers

  • Try this SQL Server alert .You need to use database mail and then the operator to send the mail ..Modify it as I have kept the > 800 value and you need to use < sign ...

    USE

    [msdb]

    GO

    /****** Object: Alert [Low disk] Script Date: 07/12/2011 13:57:50 ******/

    EXEC

    msdb.dbo.sp_add_alert @name=N'Low disk',

    @message_id

     

    @severity

    =0,

    @enabled

    =0,

    @delay_between_responses

    =0,

    @include_event_description_in

    =0,

    @category_name

    =N'[Uncategorized]',

    @wmi_namespace

    =N'\\.\root\CIMV2',

    @wmi_query

    =N'select * from __instancemodificationevent within 1 where targetinstance isa ''CIM_LogicalDisk'' and targetinstance.freespace > 800',

    @job_id

    =N'00000000-0000-0000-0000-000000000000'
     

    GO

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

    =0,
    • Marked as answer by pl80 Tuesday, July 12, 2011 1:55 PM
    Tuesday, July 12, 2011 8:28 AM

All replies

  • Here you go (note after the server reboot you need to execute it again else put this .VBS file in the windows startup):

     

    Save it as <whatever>.VBS and execute it (make sure SMTP service is running as well).

    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk")
    For Each objDisk in colDisks
        disk = objDisk.Name
        intFreeSpace = objDisk.FreeSpace
        intTotalSpace = objDisk.Size
        pctFreeSpace = intFreeSpace / intTotalSpace

    Do while(true)
    If pctFreeSpace <0.15 then
      'Wscript.Echo "Below drives are less than 15% of disk space in it"
      'Wscript.Echo disk  & " : " & FormatPercent(pctFreeSpace)


    Set objEmail = CreateObject("CDO.Message")
    objEmail.From = "xxxxx.xxxxxx@xxx.xxxxx.com"
    objEmail.To = "xxxxx.xxxxxx@xxx.xxxxx.com"
    objEmail.Subject = "Low disk space alert"
    objEmail.Textbody = "One of the disks on the server xxxxx is low in space and has less then 15% remaining "
    'objEmail.AddAttachment "C:\Documents and Settings\Abhay\Desktop\data1\team emails.txt"
    objEmail.Send

    Wscript.Sleep(600000)

    end if
    'Next
    loop
    Next


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, July 11, 2011 2:00 PM
  • I use simple then below stored procedure

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[sp_DiskAlert]

    AS

    TRUNCATE TABLE Drivetable

    INSERT into Drivetable Exec xp_fixeddrives 

    IF EXISTS (

    SELECT * FROM

    (

    SELECT Drive,[MB Free]/1024 AS GB FROM DriveTable) AS Der

    WHERE Drive='N' AND GB<=30

    )

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail 

         @profile_name = 'Profile', 

         @recipients = 'ttt@ttt.com', 

         @subject = 'SERVER1 LOW DISK Space on N drive'

    END 

    IF EXISTS (

    SELECT * FROM

    (

    SELECT Drive,[MB Free]/1024 AS GB FROM DriveTable) AS Der

    WHERE Drive='O' AND GB<=8

    )

    BEGIN

    EXEC msdb.dbo.sp_send_dbmail 

         @profile_name = 'Profile', 

         @recipients = 'ttt@ttt.com', 

         @subject = 'SERVER1 LOW DISK Space on O drive'

    END 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 11, 2011 2:18 PM
  • Check my article, it might help you.

    http://sql-articles.com/scripts/disk-space-including-mounted-drive-check-via-sql-server/


    Vidhya Sagar. Mark as Answer if it helps!
    Monday, July 11, 2011 2:42 PM
  • Vidhya,

    Your script returns an error:

    , 1) SWbemObjectSet: Invalid class echeck.vbs(11 C:\downl oads\spac

    Uri,

    I said, I don't want to use a stored procedure, I need to get an alert the moment free space drops below certain level, not check every some time.

    Abhay,

    Your script uses 40-50% of CPU: it cannot be run on a production system.

    :(

    Tuesday, July 12, 2011 7:36 AM
  • Try this SQL Server alert .You need to use database mail and then the operator to send the mail ..Modify it as I have kept the > 800 value and you need to use < sign ...

    USE

    [msdb]

    GO

    /****** Object: Alert [Low disk] Script Date: 07/12/2011 13:57:50 ******/

    EXEC

    msdb.dbo.sp_add_alert @name=N'Low disk',

    @message_id

     

    @severity

    =0,

    @enabled

    =0,

    @delay_between_responses

    =0,

    @include_event_description_in

    =0,

    @category_name

    =N'[Uncategorized]',

    @wmi_namespace

    =N'\\.\root\CIMV2',

    @wmi_query

    =N'select * from __instancemodificationevent within 1 where targetinstance isa ''CIM_LogicalDisk'' and targetinstance.freespace > 800',

    @job_id

    =N'00000000-0000-0000-0000-000000000000'
     

    GO

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

    =0,
    • Marked as answer by pl80 Tuesday, July 12, 2011 1:55 PM
    Tuesday, July 12, 2011 8:28 AM
  • Abhay,

    This works well.  Thanks.

    (Add =0 after @message_id)

    How do I set it to check for ex. the D: drive?

    What unit is the targetinstance.freespace ? (bytes?)

    Tx.

    Tuesday, July 12, 2011 10:34 AM
  • Yes its in bytes .Try this : select * from __instancemodificationevent within 1 where targetinstance isa 'CIM_LogicalDisk' and targetinstance.freespace > 800 and targetinstance.name='C:'  (Its the drive name .)

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Proposed as answer by chaoma Wednesday, February 13, 2013 12:04 AM
    Tuesday, July 12, 2011 11:32 AM
  • Also I am using within 1  which will make this alert fire every 1 second ...normally people keep 300 which is 5 mins .otherwise you will get a lot of mails ,,
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Tuesday, July 12, 2011 11:38 AM
  • Thanks Abhay,

    e-mail subject line is 'SQL Server Alert System: 'Low disk' occurred on \\'

    Is there any way we can get the server name like 'SQL Server Alert System: 'Low disk' occurred on \\Server_Name'

    Monday, December 24, 2012 8:04 PM
  • @notification_message=N'low disk space on drive c:\ on server: ABC',
    Wednesday, February 13, 2013 12:05 AM
  • Hi Abhay,

    Thanks for the post this is so important for me at Client location , I tried the code with slight modification, but never received email(the operator is working for my failed jobs so no problem with the operator). The server name is default(no instance installed).

    Thanks for the help.

    USE [msdb]
    GO

    EXEC msdb.dbo.sp_add_alert  @name=N'Low disk',
    @message_id=0,
    @severity=0,
    @enabled=0,
    @delay_between_responses=0,
    @include_event_description_in=0,
    @category_name=N'[Uncategorized]',
    @wmi_namespace=N'\\.\root\CIMV2',
    @wmi_query=N'select * from __instancemodificationevent within 1 where targetinstance isa ''CIM_LogicalDisk'' and targetinstance.freespace <10737418240 and targetinstance.name=''D:''',
    @job_id=N'00000000-0000-0000-0000-000000000000' 
    GO


    From Harish


    Friday, February 07, 2014 8:59 PM