Creating an Alert

By creating alerts, you can set up SQL Enterprise Manager to respond automatically to SQL Server events, either by executing a task that you have defined or by sending an email and/or a pager message to an operator that you have specified.

Although it is not required, it is a good idea to create operators before you create alerts.

    To create an alert

1. 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.

   

2. Choose the New Alert button.

   

   The New Alert dialog box appears.

   

3. In the Name box, type a name to assign to this alert.
4. Optionally, to create an inactive 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.

5. 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. For more information, see Managing Messages, later in this chapter.

     

   Note that an alert will occur only if the specified event is written to the Windows NT application log. For information about which events are logged, see Alerts Overview.

   For a list of error numbers and a discussion of severity levels, see Part 8, Troubleshooting.

   If an event occurs that matches both an alert set on a error number and an alert set on a severity, only the more specific alert (the error number alert) will run.

6. 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.
7. 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 program or a Transact-SQL statement (task types CmdExec or TSQL) and can only have an "On Demand" schedule. 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 notification message, choose Include Error Message Text In Notifications, and then select the Email 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.

8. 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 Email and/or for Pager. Repeat for each operator to be notified.

   A solid envelope or solid telephone icon indicates that the operator 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 you have not yet created the necessary operators, don't worry. You will be given the opportunity during operator creation to assign this alert to each operator. You can also later edit this alert after operators have been created.

9. Choose OK.

   The alert is created.