Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Automate SQL Server Monitoring with Email Alerts

MSSQLTips author Ben Snaidero By:   |   Read Comments (13)   |   Related Tips: More > Monitoring
Problem

All DBA's perform some sort of monitoring of their SQL Server database instances as no one likes to find out from a user that there is an issue with the database. In an ideal world we would notice any issues before they occur and have a fix in place so the users of our systems don't even know there was a problem. This tip will extend a previous tip I wrote, which gave you queries that you could run manually to check on your system, by outlining a low tech way to monitor your SQL Server database instances without having to login yourself and run any T-SQL manually.

Solution

Our solution will use two built in features of SQL Server to automate our database server checks. The SQL Agent will be used to schedule and run the T-SQL that performs the checks and database mail will be used to email out any alerts. Tips regarding creating and configuring these two features can be found using the links below as I won't go into any of these details in this tip.

SQL Agent

Database Mail

Setup the SQL Server Monitoring

Now let's start setting up our monitoring job. Each item to be checked is created as its own job step and each job step follows the same basic template.

  1. Run query to gather data and store in a temporary table. This could be performance related (i.e. "batch requests/sec") or some type of report (listing of failed jobs)
  2. Run query to check if temporary table data violates threshold (i.e. "low disk space") or has records (i.e. there are jobs that failed)
  3. If step 2 triggers alert then send email

Below is an example which checks the number of batch requests per second. My code for sending mail in html format is taken from books online and another good explanation of this can be found here.

declare @inibrps bigint
declare @brps decimal(38,2)
select @inibrps=cntr_value 
from sys.dm_os_performance_counters
where counter_name LIKE 'Batch Requests/sec%'
waitfor delay '000:00:10'
select @brps=(cntr_value-@inibrps)/10.0
from sys.dm_os_performance_counters
where counter_name like 'Batch Requests/sec%'
if (@brps > 1000)
begin
 declare @strsubject varchar(100)
 select @strsubject='Check batch requests/sec on ' + @@SERVERNAME
 declare @tableHTML  nvarchar(max);
 set @tableHTML =
  N'<h1>Batch Request rate - ' + @@SERVERNAME +'</h1>' +
  N'<table border="1">' +
  N'<tr><th>Batch Requests/sec</th></tr>' +
  CAST ( ( SELECT td = @brps
      FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'</table>' ;
 EXEC msdb.dbo.sp_send_dbmail
 @from_address='test@test.com',
 @recipients='test@test.com',
 @subject = @strsubject,
 @body = @tableHTML,
 @body_format = 'HTML' ,
 @profile_name='test profile'
end

Using the above as a template we are now able to automatically monitor anything on our server that can be checked using T-SQL. Here are links to some examples using queries from my last tip. In all cases we only send exception emails, that is, when there is some sort of issue that needs our attention.

Now that we have some items to check we can then create a job specifying as many steps as we like. Below are a few screenshots of the job and job steps. You can find the complete script to create this job here.

SQL Server Agent Monitoring Job General Tab


SQL Server Agent Monitoring Job Steps Tab


SQL Server Agent Monitoring Job Notifications Tab

Explanation of SQL Server Agent Job Configuration

The first thing to take note of is that I have set each job step to move onto next step even after a failure. This is done to ensure that we still complete all the other checks even if one or more of the steps fail. Also notice that I've added one step at the end of the job which sends an email that the server check has been completed. If you are monitoring a large number of servers then you may want to remove this step to avoid being bombarded with emails and use some third party tools to monitor the event log and/or SQL Server for failed jobs. Below is the code for this final step. You could also do this using the notifications section of the job itself, but doing it this way allows you to customize the message.

declare @strsubject varchar(100)
select @strsubject='SQL check completed on ' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@from_address='test@test.com',
@recipients='test@test.com',
@subject = @strsubject,
@profile_name='test profile'

SQL Server Monitoring Email Alerts

Finally let's take a look at what is contained in the email alert messages. All email alerts follow the same simple format. The subject of the email will tell what the issue is and on which server. The body contains more detailed information about the error in a tabular format (i.e. which drive is low on space, error message for the failed job step, etc.). Here are a couple samples:

SQL Server Monitoring Email listing missing backups
SQL Server Monitoring Email listing drives with less than 1 GB
Next Steps


Last Update: 12/8/2011


About the author
MSSQLTips author Ben Snaidero
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, December 08, 2011 - 10:44:11 AM - Ankit Shah Read The Tip

Nice Job Ben,

  It's working smoothly. Do you have Template for locks and  Deadlock notification if you have please can you post it here ?

Thanks again


Thursday, December 08, 2011 - 10:59:03 AM - Ben Snaidero Read The Tip

Hi Ankit,

Sorry I don't have a monitoring template related to locking.  If you did want to do something like this you would have to check much more frequently that once or twice a day.  Check out this link, http://www.mssqltips.com/sql-server-tip-category/61/locking-and-blocking/, I am sure there is information in these tips that could get you started on creating your own.

Ben.


Friday, December 09, 2011 - 8:46:22 AM - Arnold Read The Tip

Is it possible to consolidate all of the logic into 1 job step to have only 1 email sent with all of the information???

Great information even if it is multiple steps.


Friday, December 09, 2011 - 9:37:51 AM - Ben Snaidero Read The Tip

Hi Arnold,

It is possible to put all the logic into one step.  You'd just have to keep appending to the body of the email as you do each check and then send one email at the end.  I prefer to have mulitiple steps just because it's easier to modify later on as you won't break other steps if you make changes to an existing item or add a new item to check.

Thanks for reading.

Ben.


Tuesday, December 20, 2011 - 11:39:42 AM - Jason Read The Tip

In SQL Server world, monitoring blocling is also very important. When you have frequesntly blocking, you should further find out how to improve the system.

Jason

http://dbace.us

 


Wednesday, September 12, 2012 - 2:18:46 AM - SQLDBA Read The Tip

Great post ..Thanks


Sunday, October 21, 2012 - 7:39:09 AM - Andrea Read The Tip

Very good post, but is possibile add T-SQL for get last 10 query more expensive or longer time with mail alert?

 

 


Sunday, October 21, 2012 - 10:06:14 AM - Ben Snaidero Read The Tip

Hi Andrea

Anything that can be queried using TSQL could be added to this monitoring and sent out as an email alert.   Sinply use one of the examples as a template and substitute in your own query.

Thanks for reading

Ben


Monday, December 24, 2012 - 10:26:39 AM - Sonny Read The Tip

Hi Ben,

Thanks for this fine post, it was so helpfull to me. I am trying to use these monitoring jobs on a Win2008 R2 SQL Server Standard Edition x64

I have configured and tested Database Mail and I receive by email these nice HTML messages, only problem is not all the jobs are working (for ex. the failed job one). From the task history I have the following errors:

Step Name  Check Missing Backups

Message
Executed as user: NT AUTHORITY\SYSTEM. There is already an object named 'Missing_Backups' in the database. [SQLSTATE 42S01] (Error 2714).  The step failed.

Step Name  Check Failed Jobs

Message
Executed as user: NT AUTHORITY\SYSTEM. There is already an object named 'Failed_Jobs' in the database. [SQLSTATE 42S01] (Error 2714).  The step failed.

Step Name  Check SQL Error Log

Message
Executed as user: NT AUTHORITY\SYSTEM. There is already an object named 'SQL_Log_Errors' in the database. [SQLSTATE 42S01] (Error 2714).  The step failed.

Any ideea to get these checks working also?

Thanks and happy holidays!


Monday, December 24, 2012 - 8:58:26 PM - Ben Snaidero Read The Tip
Hi Sonny Not sure what exactly the issue you might be facing is. I tested these without a problem. Only thing I can think of is you missed the drop statements when copying the scripts? Thanks for reading Ben

Tuesday, December 25, 2012 - 4:39:16 PM - Sonny Read The Tip

Hi Ben, it seems the steps: check failed jobs and check missing backups need to be run on "msdb" database, on "master" they will fail with errors.

After I corrected this the job runs smoothly, hopefully it can help other people. Thanks again for this post.


Wednesday, January 02, 2013 - 12:50:55 AM - erfan Read The Tip

 

1.       Apart from job activity monitor, are there any other options (may be single query/ a procedure etc) to identify the currently running jobs or failed jobs. Executing this should give details of running/failed jobs.

2.       Also identify where the error logs have been stored in the jobs, whether it is a default storage or any specific log tables used.

 


Thursday, March 28, 2013 - 5:11:25 PM - Oz Read The Tip

Hi ben,
Great article, very useful!
I'm sorry for the dumb question, but I couldn't get the output of each step within the mail body.
I got a blank email.
Am I missing something here?

I don't want to use the @query parameter in the sp_send_dbmail and att the step content as part of the mail sending.

Thanks again,

 

Oz



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.