?
Solved

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

Posted on 2012-09-12
2
Medium Priority
?
843 Views
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
0
Comment
Question by:goprasad
2 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 750 total points
ID: 38393037
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
 
LVL 40

Accepted Solution

by:
lcohan earned 750 total points
ID: 38395614
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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