Automate SQL Server Monitoring with Email Alerts

By:   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Monitoring


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.


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)
 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 
  N'</table>' ;
 EXEC msdb.dbo.sp_send_dbmail
 @from_address='[email protected]',
 @recipients='[email protected]',
 @subject = @strsubject,
 @body = @tableHTML,
 @body_format = 'HTML' ,
 @profile_name='test profile'

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='[email protected]',
@recipients='[email protected]',
@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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, March 28, 2013 - 5:11:25 PM - Oz Back To Top (23063)

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,



Wednesday, January 2, 2013 - 12:50:55 AM - erfan Back To Top (21226)


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.


Tuesday, December 25, 2012 - 4:39:16 PM - Sonny Back To Top (21130)

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.

Monday, December 24, 2012 - 8:58:26 PM - Ben Snaidero Back To Top (21128)
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

Monday, December 24, 2012 - 10:26:39 AM - Sonny Back To Top (21120)

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

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

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

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!

Sunday, October 21, 2012 - 10:06:14 AM - Ben Snaidero Back To Top (20016)

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


Sunday, October 21, 2012 - 7:39:09 AM - Andrea Back To Top (20014)

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



Wednesday, September 12, 2012 - 2:18:46 AM - SQLDBA Back To Top (19478)

Great post ..Thanks

Tuesday, December 20, 2011 - 11:39:42 AM - Jason Back To Top (15400)

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.



Friday, December 9, 2011 - 9:37:51 AM - Ben Snaidero Back To Top (15328)

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.


Friday, December 9, 2011 - 8:46:22 AM - Arnold Back To Top (15326)

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.

Thursday, December 8, 2011 - 10:59:03 AM - Ben Snaidero Back To Top (15319)

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,, I am sure there is information in these tips that could get you started on creating your own.


Thursday, December 8, 2011 - 10:44:11 AM - Ankit Shah Back To Top (15318)

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

get free sql tips
agree to terms