Wednesday, June 8, 2011

Setting up SQL Server to email you when a SQL job fails

In this post I will list all the steps needed to be completed in order to have SQL Server send email notifications when a job fails.

Step 1.
Enable Database Mail unless it has already been done. Make sure you have a profile set up. I will not go into details on this step as it's pretty straight-forward. Make sure your test email will go through though. In our case our STMP server only accepts email messages from specific servers so if I set up database mail on a new SQL Server then I will have to notify our server admin to add that server to the white list.

Step 2.
Create an Operator. This item is found under SQL Server Agent in SSMS.
This step is pretty straight-forward as well. You need to do is name the operator and specify an email address for it. At my current job we do not have a large group of database admins so I only have one operator with a distribution group email address that goes to exactly 2 people.

Step 3.
Enable mail profile for SQL Server Agent. This can be done in the Properties of SQL Server Agent, Alert System section. Check the Enable mail profile item, select Database Mail as the Mail system and select the Mail profile that was created in Step 1. In this section you may enable the a fail-safe operator as well by selecting the Operator you created in Step 2.

Step 4.
Restart SQL Server Agent! This bites me all the time. You do everything but this step and you wonder why it does not work. Because you forgot to restart the Agent!

Step 5.
Edit the properties of the SQL Job for which you want to set up email notifications. In the Notifications section check E-mail and specify the Operator you set up in Step 2. You obviously want the notification to be sent out when the job fails. However, in some unique scenarios you also want notifications when the succeeds or simply just completes. I usually also check the Write to the Windows Application event log as well. Emails can get deleted. You need to make of an effort to get rid of an event log. Save the SQL Job and you are done!