Anatomy of a SQL Agent Email Notification

By: Joe Gavin |  |   Related Tips:More >SQL Server Agent

Problem

You’re troubleshooting and / or reconfiguring a SQL Server Agent Email Notification and find that a workflow would be helpful. While SQL Server Agent and Database Mail are fairly straight forward you can find yourself going around in circles clicking around SQL Server Agent Properties, SQL Server Agent Operator Properties, Database Mail Profiles and Database Mail Accounts to troubleshoot and fix a problem.

Solution

We’ll take a ‘Neck Bone Connected to the Head Bone’ approach to step through an example of an existing job from start to finish looking at where things are configured via SQL Server Management Studio.

Following are each of the areas we’ll review:

• SQL Server Agent Job Notification – SQL Agent Operator ‘email to’ and conditions to send on (succeeds, fails, completes) set here
• SQL Server Agent Operator – Maps an Operator name to an email address
• SQL Server Agent Database Mail Profile – Mail profile enabled and configured here – forgetting to enable the profile is a very easy mistake to make on an initial configuration
• Database Mail Profile – Points to a list of one or more Database Mail Accounts
• Database Mail Account – Conduit to an SMTP server

Let’s look at each of the pieces that make up a SQL Agent Email Notification in a bit more detail.

SQL Server Agent Job Notification

SQL Agent Operator email to and conditions to send on (succeeds, fails, completes) set here.

Navigation

• In SQL Server Management Studio, expand SQL Server Agent Jobs
• Right click on your Job
• Select Properties

• Notifications

In our example a SQL Agent Job is configured to send an email when the job completes regardless of success or failure. The email originates here and is passed to a SQL Agent Operator called SQLAlerts_operator configured in the SQL Agent Job Properties.

SQL Server Agent Operator

Maps the SQL Server Agent Operator to an email address.

Navigation

• In SQL Server Management Studio, expand the SQL Server Agent dropdown
• Expand the Operators dropdown
• Right click on your Operator
• Select the Properties option

• General

The email is received from the SQL Agent Job Notification and is passed to an email address, typically a distribution list, configured here.

SQL Server Agent Database Mail Profile

Mail profile enabled and configured here. Forgetting to enable the profile is a very easy mistake to make on an initial configuration.

Navigation

• In SQL Server Management Studio, tight click on SQL Server Agent
• Select the Properties option

• Alert System

The SQL Agent Database Mail Profile configured here in SQL Agent Alert System Properties passes the email to a Database Mail Profile in Database Mail.

Database Mail Profile

Points to a list of one or more Database Mail Accounts.

At this point we’ve left the SQL Server Agent and are in Database Mail. The Database Mail Profile Properties are where the Profile is pointed to one or more SMTP accounts.

Navigation

• In SQL Server Management Studio, expand the Management dropdown
• Right click Database Mail
• Select the Configure Database Mail option

• Manage Database Mail accounts and profiles radio button
• Click the Next button to proceed

• View, change, or delete existing profile…
• Click the Next button to proceed

The Database Mail Profile points to one or more SMTP accounts and starts with the first Account and continues through the others in the event it cannot be sent through the earlier SMTP server(s) in the list. We’ll keep it simple here and use one Account.

Database Mail Account

Conduit to from Database Mail to an SMTP server

Navigation

• In SQL Server Management Studio, expand Management dropdown
• Right click on Database Mail
• Select the Configure Database Mail option

• Manage Database Mail accounts and profiles radio button
• Click the Next button to proceed

• View, change, or delete existing account
• Click the Next button to proceed

The Database Mail Account is where we configure:

• Sent from
• Display name
• Reply to (optional)
• SMTP server address
• Authentication info (if required)

The Database Mail Account passes the email to the SMTP server and off it goes.

Troubleshooting

Next, we’ll troubleshoot an issue where we know the SQL Agent Job ran and we expected an email but didn’t receive it. Now that we understand the Notification workflow and where everything is configured we can make light work of it.

First, navigate to the SQL Agent Job Properties and check the conditions of when we expect to see an email.

Navigation

• Right click on the SQL Server Agent Job
• Properties
• Notifications

Here we confirm an email should be generated upon completion of the Job regardless of success or failure.

Now, let’s confirm the Operator is enabled.

Navigation

• Expand the Operators dropdown
• Right click on the Operator
• Properties

Verify Operator ‘Enabled box’ is checked and E-mail name is correct.

Verify Database Mail is enabled and we’re using the correct Profile

Navigation

• Right click on SQL Server Agent
• Properties

• Alert System

So far all looks good with the SQL Agent. Now it’s time to look at Database Mail.

Navigation

• Right click Database Mail
• Click Send Test E-Mail…

Check Database Mail Profile is correct, enter a valid email and click Send Test E-Mail

Hmmm, still no email. If we received an email at this step we would go back and look at the SQL Agent. Where it didn’t work we’ll continue on in Database Mail.

Navigation

• Go back and right click on Database Mail again
• View Database Mail Log

We’re on to something. That red X doesn’t look good. The message text is telling us it tried to send to an unknown host. I’ll bet that’s it.

Let’s check our mail host configuration.

Navigation

• Expand Management dropdown
• Right click on Database Mail
• Select the Configure Database Mail option

• Configure Database Mail
• Manage Database Mail accounts and profiles radio button
• Click the Next button to proceed

• View, change, or delete existing profile…
• Click the Next button to proceed

What? Smtp.mailgun.orf? That can’t be right.

Correct it, then click the Next button to Finish.

In our example I know the job only sends an email and doesn’t do anything else we can just manually run it again to test the change.

Navigation

• Expand Jobs dropdown
• Right click on Job
• Start Job at Step…

Note: The success or failure of the SQL Agent Job is independent of success or failure of the Database Mail Notification. In other words, if the Agent Job reports it was successful, it doesn’t mean the Notification was.

Here it is. The email was received as expected. Problem solved.

While this example only shows one minor configuration problem, the troubleshooting methodology is basically the same:

• Make sure Agent Job should have sent a Notification
• Step through SQL Agent config
• Step through Database Mail config

Next Steps

Following are links to a plethora of tips on SQL Server Agent and Database Mail:

• https://www.mssqltips.com/sql-server-tip-category/27/sql-server-agent/
• https://www.mssqltips.com/sql-server-tip-category/44/database-mail/

And here are links to Microsoft documentation an SQL Agent and Database Mail stored procedures:

• https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/database-mail-stored-procedures-transact-sql?view=sql-server-2017
• https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sql-server-agent-stored-procedures-transact-sql?view=sql-server-2017

Last Update: 2018-07-25

About the author