Monday, March 30, 2009

SQL Server Agent and Database Mail – Better Together

SQL Server Agent and Database Mail – Better Together

This post is how to configure SQL Agent to use Database Mail for sending an email to an operator in the event a job fails. I was hoping that there was a single MSDN topic on how to do this, but no such luck. The steps are fairly straight forward, but I managed to get hung up on one of the help topics that describes the process. So I’m writing this post so others don’t fall into the same trap.

Step 2. This is where I got a bit confused. There is an BOL topic on Using Database Mail with SQL Server Agent on MSDN that’s a bit confusing. The first two steps in the instructions for configuring Database Mail are covered when you run through the wizard as described in Pinal Dave’s posting in step 1 above. The next two steps talk about configuring the profile and adding user permissions to a role DB role in msdb. You can skip these steps and go to step 5 – How to: Configure SQL Server Agent Mail to Use Database Mail. It lays out:

In Object Explorer, expand a server.

Right-click SQL Server Agent, and then click Properties.

Click Alert System.

Select Enable Mail Profile.

In the Mail system list, select Database Mail.

In the Mail profile list, select a mail profile for Database Mail.

Restart SQL Server Agent. The critical step!

Step 3. Setting up an operator is fairly straight forward. You can use the UI to set the Name and the E-mail name to get going. Just right click on the Operators node under SQL Server Agent and issue the New Operator command. You can also go the T-SQL route:

Step 4. Finally, you then go to the job you want to be notified about, bring up the Properties page, and go to the Notifications page. Then complete the dialog as shown below with any additional options you would like.