Using Database Mail to send alerts when SQL Server Agent jobs fail

Peter Schmitz

Administrator
Staff member
Last week, I wrote a brief article on how to configure SQL Server 2008 R2 Database Mail. This brief article will use that configuration and use it to alert us of errors when a SQL Server Agent job fails, by means of an automated E-mail.

To do so, right-click SQL Server Agent (The MSX suffix in the screenshot is due to the Agent on this particular server acting as the Master in a multi-sevrer job administration) and select "Properties":

SQLServerAgent_Mail1.png

In the SQL Server Agent properties, head to the "Alert System" page, and there, in the Mail Session part of the page, check the box next to "Enable mail profile" to enable mail, and under "Mail System", set it to "Dabatase Mail". Under "Mail Profile", set it to "SQL Server Mail":

SQLServerAgent_Mail2.png

Click "OK", right-click the "Operators" folder under SQL Server Agent, and select "New operator":

SQLServerAgent_Mail3.png

Define a name for the profile, and enter the mail account you want the mail to be sent to:

SQLServerAgent_Mail4.png

To ensure things will work correctly, it is usually a good idea to restart SQL Server Agent at this stage. This can be done either from SQL Server Configuration Manager, or directly from SQL Server Management Studio (SSMS) by right-clicking SQL Server Agent, and then selecting "Restart":

SqlServerAgent_Restart.png

In order to verify things work, let's set up a new job that we will ensure will fail, and configure it to send a mail in case of a failure.

Right-click the Jobs folder, and then select "New Job":

SQLServerAgent_Mail5.png

In the General section, the only thing I filled out was the name: "Failing Job". In the "Steps" section, I created a new job step that selects data from a non-existing table in a non-existing database:

SQLServerAgent_Mail6.png
Then, on the "Notifications" page, I selected to have a mail sent to the profile created earlier if the job fails:

SQLServerAgent_Mail7.png
That's it. Click "Ok". To test whether everything works, right-click the newly created job "Failing Job", and then select "Start Job At Step...":

SQLServerAgent_Mail8.png

The job will start executing, and predictably, fail:

SQLServerAgent_Mail9.png

If all went well, you should receive a mail notifying you of the failure:

SQLServerAgent_Mail10.png

Summary: In this article, I showed you how to use Database Mail to automatically receive mails when a SQL Server Agent job fails.
 
Top