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":
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":
Click "OK", right-click the "Operators" folder under SQL Server Agent, and select "New operator":
Define a name for the profile, and enter the mail account you want the mail to be sent to:
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":
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":
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:
Then, on the "Notifications" page, I selected to have a mail sent to the profile created earlier if the job fails:
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...":
The job will start executing, and predictably, fail:
If all went well, you should receive a mail notifying you of the failure:
Summary: In this article, I showed you how to use Database Mail to automatically receive mails when a SQL Server Agent job fails.
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":
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":
Click "OK", right-click the "Operators" folder under SQL Server Agent, and select "New operator":
Define a name for the profile, and enter the mail account you want the mail to be sent to:
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":
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":
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:
The job will start executing, and predictably, fail:
If all went well, you should receive a mail notifying you of the failure:
Summary: In this article, I showed you how to use Database Mail to automatically receive mails when a SQL Server Agent job fails.