Anatomy of a SQL Agent Email Notification


By:   |   Updated: 2018-07-25   |   Comments   |   Related: 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
object explorer
  • Notifications
job properties

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
object explorer
  • General
sql alerts

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
properties
  • Alert System
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
configure database mail
  • Manage Database Mail accounts and profiles radio button
  • Click the Next button to proceed
select configuration task
  • View, change, or delete existing profile…
  • Click the Next button to proceed
manage profiles
manage existing profile

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
object explorer
  • Manage Database Mail accounts and profiles radio button
  • Click the Next button to proceed
configuration task
  • View, change, or delete existing account
  • Click the Next button to proceed
manage profiles accounts
manage existing account

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. 

email

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
select a page

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
object explorer

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

sql alerts

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

Navigation

  • Right click on SQL Server Agent
  • Properties
properties
  • Alert System
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…
object explorer

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

mail test

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

log file view

Let’s check our mail host configuration.

Navigation

  • Expand Management dropdown
  • Right click on Database Mail
  • Select the Configure Database Mail option
database mail
  • Configure Database Mail
  • Manage Database Mail accounts and profiles radio button
  • Click the Next button to proceed
configuration task
  • View, change, or delete existing profile…
  • Click the Next button to proceed
profiles

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

manage existing account

Correct it, then click the Next button to Finish.

manage existing account

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…
object explorer
start jobs

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.

job succeeded

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:



Last Updated: 2018-07-25


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables








get free sql tips
agree to terms