Anatomy of a SQL Agent Email Notification

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:

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

Resources

Community

Subscribe