Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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 Server Agent Job Management
- Running SQL Server Agent with least privilege account
- Disabling-Enabling SQL Server Agent Jobs
- SQL Server Agent Job Categories
- 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.
- 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)
- 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)
- If step 2 triggers alert then send email
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=([email protected])/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:
- 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
Last Update: 2011-12-08
About the author
View all my tips
- How to setup SQL Server alerts and email operator ...
- Automate Monitoring SQL Server Error Logs with Ema...
- Automate SQL Server Monitoring with Email Alerts...
- Monitor SQL Server Database File Growth with WMI A...
- Monitor SQL Server Databases Changes Using WMI Ale...
- Monitor SQL Server Master Database Changes with WM...
- More SQL Server DBA Tips...