Configuring SQL Server Agent Operators

SQL Server provides a great notification system for alerts and jobs. There are many options to be set and multiple dialog boxes to step through, but once finished, you’ll always be the first to know when a problem is immanent. Below is a detailed reference to get this running on a new server.

  1. First thing’s first: Set up Database Mail. This a wonderfully illustrated tutorial that is consistent for SQL Server 2005 and 2008.
  2. Create a new operator.
    Right-click the Operators folder in the Object Explorer window. It will be nested inside SQL Server Agent.

    Create New Operator

    Create New Operator

  3. Configure the new operator. There are only a couple of items that need to be set:
    1. Name: A label for either the person or group name. It need not match the actual email account name.
    2. E-mail name: Enter the email address of the person or group who you wish to receive this notification. Separate multiple entries with a semicolon (;).
    Operator Settings

    Operator Settings

    While there are additional notification options such as pager and net send, they have been deprecated and will be removed in future versions.

  4. Configure SQL Server Agent
    Right-click the SQL Server Agent tree item and select properties from the resulting dropdown list.

    SQL Server Agent Properties

    SQL Server Agent Properties

  5. Switch to the Alert System pane and make several changes there.
    1. Enable mail profile: Ensure this is checked.
    2. Mail system: Database Mail. SQLMail has been deprecated and we have not configured it in this guide.
    3. Mail profile: Select the profile you created when you set up Database Mail.
    4. Enable fail-safe operator: Check this box.
    5. Operator: Select the operator you created above. The name should be listed in the dropdown box.
    6. Notify using: E-mail. Again, pager and net send have been deprecated.

    SQL Server Agent Alert settings

    SQL Server Agent Alert settings

  6. Restart SQL Server Agent. This is required.
  7. Configure the SQL Server Agent Job
    1. Locate a job which you want to configure notification for.
    2. Open the properties dialog box and switch to  the Notification Pane.
    3. Check the E-Mail box and select the appropriate operator and “When the job fails”
    Job Notification Settings

    Job Notification Settings

    When the job does not succeed for any reason, you will receive an email telling you so. You can also set options for “When the job succeeds” and “When the job completes”. You may want to fiddle with these settings to test that the job is configured correctly.

9 comments on “Configuring SQL Server Agent Operators

  1. Great one…
    also excec sp_configure ‘Show Advanced options’,1
    reconfigure
    excec sp_configure ‘Database Mail Xps’,1
    reconfigure

  2. Pingback: Creating an automated database backup by means of SQL Server Agent Job « Placko's SQL KB

  3. srikanth on said:

    Hi, Really usefull info…thanks, But small qry
    If job succeeds then send mail to person 1 other wise send mail to person 2 ?

  4. E-mail name: Enter the email address of the person or group who you wish to receive this notification. Separate multiple entries with a semicolon (;).

  5. kevin benedict on said:

    how do you get the job to notify more than one person?

  6. John Stanley on said:

    That was really helpful. Have been trying to get Operator email alerts working and haven’t until this tutorial. Well done & thanks.

  7. daniel alvarado on said:

    Thanks a lot, this was really simple and effective. 10/10

  8. thanks for u r great idea to publish

  9. Really usefull info…thanks!! keep on posting

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>