Setting Up Alerts for Replication

SQL Enterprise Manager and the SQL Executive service provide an easy way to set alerts on SQL Server events. SQL Executive monitors the Windows NT application log, watching for an event that qualifies as one of the defined alerts. If such an event occurs, SQL Executive will respond automatically, either by executing a task that you have defined or by sending an e-mail and/or a pager message to an operator you have specified. You can use this capability to set up alerts on replication events.

To create an alert

From the Server Manager window, select a server, and then from the toolbar, choose the Manage Alerts button.

The Manage Alerts window appears, with the Alerts tab selected.
Choose the New Alert button.

The New Alert dialog box appears.
In the Name box, type a name to assign to this alert.
Optionally, to create an disabled alert, clear the Enabled box.
The Enabled box is selected by default. In most cases, this is the appropriate setting. It is useful to clear this option if you want to create the alert now but do not want it to take effect until later.
To define the conditions that will cause an alert, specify either an error number or a severity level.
- To specify that an alert will occur whenever an error of a specific severity level is entered in the Windows NT application log, select Severity and select an error level from the list.
- To specify that an alert will occur whenever a specific error message is entered in the Windows NT application log, select Error Number and type the error number.
  To select from a list of error messages, or to create a custom error message, select Error Number, select the browse button, and complete the SQL Server Messages dialog box that appears. In this dialog box you can view a list of messages, and you can filter the list to display only replication (or other) messages.
Note that an alert will occur only if the specified event is entered in the Windows NT application log.
For information about which events are logged, and to learn how to filter the list of messages displayed in the SQL Server Messages dialog box, see Chapter 17, Managing Alerts.
For a list of error numbers and a discussion of severity levels, see Part 8, Troubleshooting.
Optionally, to narrow the alert notification criteria further, select a database name and/or provide an error message string.
- To specify that an alert will occur only when an error is received from a specific database, choose Database Name, and select a database from the list.
- To specify that an alert will occur only when the error message contains a particular character string, type the character string in the Error Message Contains String box.
To specify the action the system will take when the defined alert occurs, complete the Response Definition options.
- To have a task execute when a qualifying alert occurs, select a task from the Task to Execute list.
  A task can invoke a command execution or a Transact-SQL statements. For information on creating tasks, see Chapter 16, Scheduling Tasks.
- To provide additional information to the alert recipients, type text (instructions or a message) in the Alert Notification Message to Send to Operator box.
  You can enter up to 255 characters. Note that this is any text you want to add to the alert. Do not type in the actual error message text.
- To include the actual text of the error message as part of the alert, choose Include Error Message Text in Notifications, and then select the e-mail and/or the Pager options.
- To set a delay between responses for recurring alerts, type a number (representing seconds) in the Delay Between Responses for Recurring Alerts box.
  For example, suppose you type 8 in this box. Later, five alerts that meet the alert definition occur within a period of eight seconds or less. Only one alert is sent to the operators specified in step 9.
To specify the operators who will be notified when the defined alert occurs, select an operator from the list under Operators to Notify, and then select the option box for e-mail and/or for Pager. Repeat for each operator to be notified.
A solid envelope or solid telephone icon indicates that the operator already has address information of that type defined. An outline envelope or outline telephone icon indicates that the operator does not have address information of that type defined.
If the necessary operators have not yet been created, the list will be empty. For instructions on creating operators, see Chapter 17, Managing Alerts. You can continue with alert creation, since you will be given the opportunity later, during operator creation, to assign this alert to each operator. You can also later edit this alert to add operators, after operators have been created.
Choose OK.
The alert is created.

The following messages can be raised by the replication subsystems, and can be used to set up replication alerts.

Message: 14150, Severity: 10, State: 0,
  Replication-subsystem: Task 'task_name' succeeded. message

Message: 14151, Severity: 20, State: 0,
  Replication-subsystem: Task 'task_name' failed. message

Message: 14152, Severity: 18, State: 0,
  Replication-subsystem: Task 'task_name' 
    scheduled for retry after number attempt(s). 'message'

Message: 14153, Severity: 10, State: 0,
  Replication-subsystem: Task 'task_name' 
    detected the potential for missing jobs on 'message'

where

subsystem: Is the replication subsystem (Distribution, LogReader, or Sync).
task_name: Is the task name.
number: Is the number of attempts.
message: Is additional information returned by the subsystem. For example, "Unable to Connect to MALAMUTE."

For more information on alerts, including information on how SQL Server alerts are generated, how to create and manage operators, and how to edit, view, and cancel alerts, see Chapter 17, Managing Alerts.