Automate SQL Server Monitoring with Email Alerts

By: Ben Snaidero   |   Updated: 2011-12-08   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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

• SQL Server Agent Job Management
• Running SQL Server Agent with least privilege account
• Disabling-Enabling SQL Server Agent Jobs
• SQL Server Agent Job Categories

Database Mail

• Setup SQL Server 2005 database mail
• SQL Server 2005 database mail configuration
• SQL Server 2005 Setup using scripts

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

• Check Failed Jobs
• Check SQL Error Log
• Check Missing Backups
• Check Drive Space
• Check Memory
• Check Connection Count
• Check Batch Request/Sec

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.

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:

Next Steps

• If you have not seen the first tip in the series (SQL Server Monitoring Checklist), be sure to check that out.
• Use Multi Server Administration feature to manage the SQL Server Agent Job from a single server
• Use third party software to monitor:
  • Windows SQL Server related services
  • SQL Server instance internals
  • Windows event log

About the author

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