sqlnIGHTS.COM

My Sql Server info repository

Disc Space Alert

Checking Your Servers Disk Space

I’ve heard arguments about who should be monitoring the disc space usage on the servers; some arguing that it is the responsibility of the infrastructure or SAN administrators, and others saying “Your the dba – so you do it.”  I suppose this just comes down to the structure or size of the organisation you work for. – the bigger it is the more likely this will be managed by another team. Either way, in my opinion, its vital that if you are the dba, that you should keep an eye on things, and know where you are at when it comes to available disc space.

There are plenty of third party monitoring tools that can give you this information, but if you don’t have the budget it’s a good idea to set up some kind of monitoring.

This is a technique I’ve used myself – basically I run a job that executes a stored procedure on a set schedule every hour and sends an email if the disc space falls below defined thresholds in the script.

 

Steps to set up an Alert to monitor disc space

The first thing that you need to do is to make sure that you have database mail enabled and you know the profile name to use. Once you have checked this is working, you simply need to create a job that runs every x minutes, depending on your preferences that executes the following stored procedure Create SP usp_CheckDiskSpaceAlert.sql

This stored procedure basically checks two thresholds. If  the free disc percentage falls between 10% and 20% it sends an email with a warning in the message body, and if the free disc percent falls below 10% it sends an email with an alert in the message body. I have two folders setup in my email account to place the different emails generated. The below image shows the ALERT email when the disc capacity falls below 10%. This is a good method of monitoring  your servers disc capacity especially if you can’t afford any 3rd party monitoring software.

email example

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s