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":


    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.

Share This Page

Users found this page by searching for:

  1. failed to send alert notifications

  2. sql server agent job alert

  3. create the failure mail alert for sqlagent job

  4. configure db mail alert after backup agent fails,
  5. send sql job failure notification email using cmdexec commands,
  6. sql notifications for alerts to tsql,
  7. mssql email when job fails,
  8. how to set the notification message of database backup failures,
  9. t-sql email operator when job fails