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

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
goprasadAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
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:

http://support.microsoft.com/kb/324796

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;
0
 
David ToddSenior DBACommented:
Hi,

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.

Otherwise:
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 )
Next

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.

HTH
  David

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.