Once configured, SQL Mail is the foundation for your operator notifications as well as mentioned earlier. To set up operator notifications, in Enterprise Manager open the Management, SQL Server Agent object tee.

Right-click on the Operators object, then select New Operator. See the figure below.

Setting up the operator lets you indicate not only the email address (or distribution list) to use, but also the pager information.

You also need to indicate what types of things the operator will be notified for. As an example, you can select which operators are responsible for different types of errors. In   the figure below, you can see that this operator has been selected to be notified for tempdb full errors.

By carefully assigning specific conditions to individual operators, you can control who will respond to a specific condition – using the operator’s strengths (and system rights) to your advantage.

You set up the alerts and tasks that can be responded to in the SQL Agent Alerts and Tasks object trees. As you do, you have the opportunity to select the responding operator (from those already configured on the server) from the task and job setup dialogs as well as the operator dialogs shown above. Select the Response tab from the New Alert Properties dialog to indicate the operator that should handle the alert condition.

One interesting aspect of setting up the alert notification here instead of in the operator dialog is that you can also add another message to the notification. For example, you may want to add in the phone number of the operator’s supervisor in case they get hung up on an issue and need to contact someone for additional help.

These same types of notification abilities are available at the Job level. When you are setting up a job, select the Notifications tab from the New Job Properties dialog. You can pick the responding operators and select the conditions under which that operator should be notified. See  the figure below for an example.

You can control the specific conditions under which an operator will be notified of problems with jobs.

Configuring and Managing SQL Mail 1 2 3 4 5 6 7