Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

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

Discussion in 'Installation and Administration' started by Peter Schmitz, Oct 1, 2012.

  1. Peter Schmitz

    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.

Share This Page