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? In this article we look at how to setup and send a SQL job notifications email.
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 OK to 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 .
- Now that you know how to setup a SQL Job Notifications email, 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.
- SQL Server Alerts
