How to set-up alerts for disk space in SQL 2008 server

Posted on 2012-09-12
Last Modified: 2012-09-16
Hello there,

I would like to set-up alerts for disk space in SQL 2008, for example when the System drive, Database or Log volumes, or back up volume goes low (for example under 30 GB).

Can I set this up within SSMS, if so I would like to know what steps I should follow.

Please advise.

Thanks and Regards
Question by:goprasad
    LVL 35

    Assisted Solution

    by:David Todd

    Do you want a hard number or a percentage used/free?

    execute master..xp_fixeddrives

    will give the free disk in MB for each fixed disk on your server.

    So, write a script that parses that output and if resulting value is less than 30GB then get the job to fail.

    On the job, create a notification on failure that goes to your operator (that is you)

    First - get Database Mail setup
    Second get SQL Agent to use the database mail profile that you set up above
    Third create an operator
    To test: Create a job that does something meaningless like print ''; add a notification on job completion to go to your operator; start job and wait for email to appear.

    I recommend using WMI from either vbscript or powershell with something like this:
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" _
        & strComputer & "\root\cimv2")
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk")
    For Each objDisk in colDisks
          WScript.Echo "UsageDT: " & vbTab & Now
        'Wscript.Echo "DeviceID: "& vbTab &  objDisk.DeviceID      
          WScript.Echo "SystemName: " & vbTab & objDisk.SystemName
          WScript.Echo "Caption: " & vbTab & objDisk.Caption
          WScript.Echo "VolumeName: " & vbTab & objDisk.VolumeName
        'Wscript.Echo "File System: "& vbTab & objDisk.FileSystem
        'WScript.Echo "Size: " & vbTab & objDisk.Size
        'WScript.Echo "Freespace: " & vbTab & objDisk.Freespace
          WScript.Echo "SizeGB: " & vbTab & round(( objDisk.Size / 1024.0 / 1024.0 / 1024.0 ), 2 )
          WScript.Echo "UsedGB: " & vbTab & round((( objDisk.Size  - objDisk.FreeSpace ) / 1024.0 / 1024.0 / 1024.0 ), 2 )
          WScript.Echo "FreeGB: " & vbTab & round(( objDisk.FreeSpace / 1024.0 / 1024.0 / 1024.0 ), 2 )
          WScript.Echo "PercentUsed: " & vbTab & round(( 1 - objDisk.FreeSpace / objDisk.size ) * 100, 2 )

    That will output the relevant numbers about your disk. Do filter off the network and unready disks.

    I'm using this (from powershell) to log these from each of my servers to a central database. The advantage of powershell is that you can easily loop through a list of servers. Now, of course, this doesn't create an alert - yet.

    In the past I did use a vbScript called from a SQL Job - I did one job step per server disk - and if the disk result was over 80% used the vbscript failed, and I got an email. By naming the job step with the drive letter, I could tell which disk I needed to look at from the email.


    PS Conventional wisdom has that local disk performance deteriorates when the disk gets over 80% full. SAN engineers will try to say different. SSD's are different again. But all that to say that considering 80% of capacity full is a good start. A current client runs their disks to 90% of capacity.
    LVL 39

    Accepted Solution

    In SQL 2008 is not as easy as it was in SQL 2005 however I found very easy to setup a OS alert where you can choose the treshhold:

    Alternative is to write your own SQL stored proc using code like below and check each DB free space percentage and send an (email) alert if needed. You can then schedule that Alert to run every minute or so in SQL 2008.

    SELECT db_name() as DBName,
                cast(name as varchar(100)) AS DBFileName,
                cast((size/128.0) as decimal(12,4)) as SizeInMB,
                cast( (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) as decimal(12,4)) AS AvailableSpaceInMB,
                cast( ((((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128.0) * 100))) as decimal(12,4)) AS PercentFree
          FROM sys.database_files;

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now