How to setup SQL Server alerts and email operator notifications

By: David Bird   |   Comments (39)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring

Problem

Setting up a SQL Server alert to email an operator with a message is a multiple step process. You can setup database mail, define an operator and an alert, but still no email is being sent. So how do you send an alert to an operator?

Solution

Note: This solution involves restarting SQL Server Agent.

Setup SQL Server Database Mail

First setup database mail with a profile named SQLAlerts. The profile can be named anything, but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly. For procedures in setting up database mail, see this post.

Define a SQL Server Operator

Connect to the SQL Server instance using Microsoft SQL Management Studio. Expand the SQL Server Agent tree and right click on Operators and select New Operator... as shown below.

Specify an operator Name, E-mail name, and click OK

Setup SQL Server Agent Settings

Note: This step is often overlooked when creating a SQL Server alert for the first time. SQL Server Agent must be setup correctly for operators to receive an alert e-mail.

Right click SQL Server Agent and select Properties.

Select Alert System in the left pane and do the following:

• Check Enable mail profile
• Verify Mail system is Database Mail
• Verify Mail profile is SQLAlerts that we discussed above
• Check Include body of e-mail in the notification message
• Click OKto save settings.

Restart SQL Server Agent to Activate Settings

Warning: Restarting SQL Server Agent will cancel any executing jobs, so this should be done when there is no activity.

Define a SQL Server Alert

This sample SQL Server alert will send an email when the TempDB database gets larger than 0 KB. Note: I am setting this to zero, so the alert can fire. Once verified that this works, you will need to update the setting to a number that makes sense for your environment or disable this alert.

Right click Alerts and select New Alert...

On the General pane specify:

• Name: TEMPDB Growing
• Type: SQL Server performance condition alert
• Object: SQLServer:Databases
• Counter: Data File(s) (KB)
• Instance: tempdb
• Alert if counter: rises above
• Value: 0

Click on the Response pane:

• Check Notify operators
• Check E-mail for the operator

Click on the Options pane:

• For Include alert error text in select E-mail
• For Delay between responses enter 2 minutes
• Click OK

Verify SQL Server Alert is Working

The operator should receive an e-mail if not see the troubleshooting section.

Open the Alert we just setup and click on the History page. The fields should be updated when the alert is triggered as shown below.

Disable the SQL Server Alert Test

To stop the Alert from being sent every two minutes, you have two options: disable the alert or provide a more appropriate size and response times.

To disable the SQL Server alert

• You an go to the General page for the alert and remove the checkmark from Enable and click OK
• Or you can right click on Alert name and select Disable as shown below

Change SQL Server Alert Settings

To change the settings do the following:

• Click on the General page
• Change the value to a value larger than the existing TEMPDB database file save. Note: the current file size is included in the e-mailed alert.

• Click on the Options page
• Change response to 720 minutes which is 12 hours or whatever value is suitable
• Click OK

Troubleshooting a SQL Server Alert

If the operator does not receive an e-mail:

• Test database mail by sending a test e-mail to the operator's e-mail address.
• Make sure you restarted SQL Agent and it is running.

Next Steps

• Modify the alert to execute a job that captures all current connections using sp_who2 .
• Create alerts for other criteria you wished to monitor.
• Using SQL Server Management Studio, you can script the alert so you can easily create it in other instances. See this tip on how to create scripts.

About the author

David Bird has years of IT Experience working as a DBA and programmer on Windows, UNIX, and mainframes.

View all my tips