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.

The steps that I’ll walk though to get you up and running include:

1. Configuring Database Mail
2. Configuring SQL Server Agent Mail to use Database Mail
3. Setting up an Operator
4. Adding the a notification to a job

Step 1. Configuring Database Mail, you can refer to my previous post – Using Live/Hotmail/MSN for SQL Server 2008 Database Mail or SQL MVP Pinal Dave’s post for general configuration of Database Mail.

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:

1. In Object Explorer, expand a server.

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

3. Click Alert System.

4. Select Enable Mail Profile.

5. In the Mail system list, select Database Mail.

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

7. 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:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'MyOperator',
        @enabled=1,
        @pager_days=0,
        @email_address=N'Notify.Email@MyCompany.com'
GO

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.

Again, the T-SQL would look like this:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'syspolicy_purge_history',
        @notify_level_email=2, – failed job
        @notify_email_operator_name=N'billramo'
GO

SQL Agent is now configured to let you know when things don’t go right with your automated agent jobs.

Bill Ramos on SQL Server : SQL Server Agent and Database Mail – Better Together