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 thoughts on “Configuring SQL Server Agent Operators

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

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

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>