There are many after market tools available to monitor health of SQL Server. But with help of built-in tools you can actually create some pretty neat solution by yourself too. And one of the most handy tool is “Alerts” in SQL Server. With help of Alerts you can monitor SQL Server Events, Various Performance Conditions and oddly WMI events !! And what great thing about it is you can setup it to notify users and / or run specific job (stored in SQL Agent) to handle specific event.
It is very simple to setup Alerts using SQL Server Management Studio (SSMS). Since Alert notification uses Database Mail, you need to have a valid mail profile in order to be able to sent notifications. You can actually use your gmail account for testing. While back I did post about it.
First you need to create an operator which will receive an alerts via Email or Net Send or Pager. You can create new operator in SSMS –> SQL Server Agent –> Operators. Right click on Operators and select “New Operator”.
It has two simple parts, in 1st General part you provide way you want to get notification … be it Email, Net Send Address or Pager. Furthermore you can setup pager schedule. For demo, I will be using my Gmail account as operator email.
 |
 |
And in 2nd part, you setup for what alerts and jobs you want this operator to get notified. And means of notification too. If you have already created Jobs or Alerts then they will be visible here gives you convenience to select them for notification for this operator.
Once operator is created, next step is to create alert. For that goto SSMS –> SQL Server Agent –> Alerts. Right click on Alerts and select “New Alert”. This TECHNET page gives more details about different alert types. In General section you will see that you can define alert for 3 different types, SQL Server Event, SQL Server Performance Condition or WMI Event. And even for these alerts are further divided into many different categories.
You can define alert for specific database or for all databases. Further you can refine it by specific error number or by severity (which varies based on type of event type). You can even raise alert based on some error message, which I think is pretty helpful when you want to raise alert using your own code.
In Response section, you can define how you want to respond to specific alert. You can either run some specific job (defined in SQL Sever Agent) and / or notify operator using email, pager or net send.
And last section is Options, in which you specify how much interval you want to keep between alert notifications and weather you want to include alert message in Email, Pager or Net Send with some additional message.
Actually there is one more section called History, which becomes visible once you setup alert and enable it.
 |
 |
For demo, I created an alert which notified an operator (that we created previously in operator section) when database file size of Adventureworks2012 database rises above 500KB. I know it is not realistic condition but since I am feeling lazy just bare with me 
… When this condition occurs, I am notifying operator via email at interval of 1 min.
 |
 |
And if everything works fine then you should get an email …
Alert notifications are really great feature of SQL Server. It simply allows you to monitor Server health without much of effort. Only downside I see is since it relies to SQL Server Agent to sent notifications, it is not available in express edition.
That’s it for now …
It’s Just A Thought … 
