Alerts | Microsoft SQL Server 2000 Administrators Companion

3 4

An alert is an action that occurs on a server in response to an event or to a performance condition. Alerts can notify operators, cause specified jobs to be executed, and forward events to another server. An event is an error or a message that is written to the Windows NT or Windows 2000 application event log (which you can view by using the Windows NT or Windows 2000 Event Viewer). A performance condition is a characteristic of the system's performance that can be monitored by using the Windows NT Performance Monitor or the Windows 2000 System Monitor, such as CPU utilization or the number of locks being used by SQL Server. In this chapter, we will focus on the System Monitor from Windows 2000, although the Windows NT Performance Monitor is very similar.

When an event occurs, SQLServerAgent compares the event to the list of alerts you have defined, and if an alert is defined for that event, the alert is triggered. An alert for a performance condition is fired when a defined performance threshold is reached for a specified SQL Server object in System Monitor, such as the counter User Connections under the System Monitor object General Statistics. For example, you could set an alert to occur if the value for this counter reaches 50. (System Monitor is described in Chapter 36.)

NOTE
The SQLServerAgent service must be running in order for your alerts to function.

Event Message Logging

Before we look at how to create an alert for an event, let's review the types of events that cause messages to be sent to the Windows NT or Windows 2000 application event log; only these events are available to be used to set alerts. Events (or errors) with a severity level of 19 through 25 are automatically reported to the Windows NT or Windows 2000 application event log and are therefore able to trigger alerts. By default, events that have a severity level less than 19 are not logged, and therefore, an alert cannot be triggered by those events. To enable those events to be logged so that an alert can be triggered, you must use sp_altermessage, the RAISERROR WITH LOG statement, or xp_logevent to change the logging status of the event or message. In this section, you'll learn how to create a user-defined event message and how to alter that message to ensure that it will be written to the application event log.

NOTE
When a SQL Server message is logged in the Windows NT or Windows 2000 application event log, it is also logged in the SQL Server log. To view the SQL Server log in Enterprise Manager, expand the Management folder for your server, and expand the SQL Server Logs folder.

Creating a User-Defined Event Message

All information for system and user-defined messages is stored in the sysmessages table in the master database. To create a user-defined message, use the T-SQL system stored procedure sp_addmessage. The syntax is shown here:

 sp_addmessage [@msg_num =] msg_id, [@severity=] severity, [@msg_text=] 'msg_text' [,[@lang =] 'language'] [,[@with_log=] 'with_log'] [,[@replace =] 'replace']

A user-defined messag must have a message ID value (msg_id) of 50001 or greater. The severity parameter is the severity level of the error that the message refers to, ranging from 1 through 25, with higher numbers indicating higher severity of error. Only the system administrator can set severity levels 19 through 25. The msg_text parameter is the text of the error message that will appear in the application event log when the error occurs. The language parameter specifies in which language the message is written, because multiple languages can be installed with SQL Server. The with_log parameter can be set to either TRUE or FALSE, indicating whether the message will always be logged to the Windows NT or Windows 2000 application event log. The default value is FALSE. Using RAISERROR WITH LOG (described in the next section) will override this setting if it is FALSE. The replace parameter indicates that the message should replace an existing message that has the same message ID number.

Members of the public role have permission to execute sp_addmessage, but to create a message with a severity level greater than 18 or to set with_log to TRUE, you must be a member of the sysadmin role.

Let's look at an example of using sp_addmessage. The following statement creates a new message that will always be logged in the event log (because with_log is set to TRUE):

 sp_addmessage 50001, 16, "Customer ID is out of range.", @with_log = "TRUE" GO

Altering the Log Settings of an Event Message

Suppose an existing message or a message you just created does not allow logging (or you did not include the with_log parameter), as in the following example:

 sp_addmessage 50001, 16, "Customer ID is out of range.", @with_log = "FALSE" GO

If you later want to log the message, you must change the logging state of the message. To do so, use sp_altermessage to set logging to always occur, as in the following example:

 sp_altermessage 50001, WITH_LOG, "TRUE" GO

Alternatively, you could use the RAISERROR statement with the WITH LOG option to return the message to your application, as well as to the application event log and the SQL Server log. For example, the following statement sends the message 50001 with a severity level of 16 and a state value of 1, where state is a number that can be used for tracking when the error is raised in more than one place in your code:

 RAISERROR (50001, 16, 1) WITH LOG GO

MORE INFO
For more details about using RAISERROR, look up "RAISERROR" in the Books Online index and select "Using RAISERROR" in the Topics Found dialog box.

To change the logging status of a message, you can also use the extended stored procedure xp_logevent, which is located in the master database. When you use this procedure, the message is sent to the event log and to the SQL Server log, but not to the client application. An example of using this procedure is shown here:

 USE master GO xp_logevent 50002, "Customer ID out of range", warning GO

The first two parameters are required and consist of a user-defined message ID number (which, again, must be greater than 50,000) and message text that will appear in the output to the logs. The third parameter, the severity level, is optional. Severity can be one of three character strings: informational, warning, or error. The severity level setting determines what type of icon appears next to the message in Event Viewer, enabling you to quickly identify warnings or errors. For Windows 2000, an informational message has a blue "i" icon, a warning has a yellow "!" icon, and an error has a red "X" icon. If no severity is specified, the default value is informational.

Creating an Alert

Now we're ready to create an alert on an event and on a performance condition. To create an alert, you can use Enterprise Manager, T-SQL, or SQL-DMO. Again, we will look at only the Enterprise Manager and T-SQL methods because SQL-DMO is beyond the scope of this book.

Using Enterprise Manager to Create an Event Alert

In this example, we'll create an alert on a system message that already has a severity level of 24. The message will be logged by default in the event log, with no user intervention needed to change its logging status. To create the event alert, follow these steps:

In the left pane of Enterprise Manager, expand a server folder, expand the Management folder, and then expand the SQL Server Agent folder. Right-click Alerts and choose New Alert from the shortcut menu. The New Alert Properties window appears, as shown in Figure 31-17. On the General tab, type the name of the alert, which can have up to 128 characters. For this example, type IO_error_alert. The Enabled check box allows you to enable or disable the alert. Disabling an alert will cause it to not be fired, similar to disabling a job. For this example, be sure the alert is enabled. In the Type drop-down list, select SQL Server Event Alert because we want to create an alert that will be fired when a certain event occurs. (The other alert type option is SQL Server Performance Condition Alert—an example of this type of alert will be shown in the next section.) For our example, we will create an alert that will be fired when an I/O error occurs.

Figure 31-17. The General tab of the New Alert Properties window.
In the Event Alert Definition area of the New Alert Properties window, you specify the event you want to trigger the alert by selecting either Error Number or Severity, and then specifying an error number or a severity level. If a severity level is specified, all errors with that severity level will fire the alert. For this example, select Error Number and then click the Browse (...) button to search for the number. The Manage SQL Server Messages dialog box appears, as shown in Figure 31-18.
To search for a specific error, you select a category in the Severity list box on the Search tab, and click Find. The errors found are listed on the Messages tab. The two check boxes at the bottom of the Search tab can be used to limit the search. The Only Include Logged Messages check box allows the search to retrieve messages that are automatically logged in the event log. The Only Include User-Defined Messages check box limits the search to only those messages defined by users. For our example, we want to find all fatal hardware errors, so select 024 - Fatal Error: Hardware Error in the Severity list box, and then click Find. Error number 823 (with a severity level of 24) appears on the Messages tab, as shown in Figure 31-19.

Figure 31-18. The Search tab of the Manage SQL Server Messages dialog box.

Figure 31-19. The Messages tab of the Manage SQL Server Messages dialog box.
Click OK to accept this message and to return to the General tab of the New Alert Properties window. The Database Name drop-down list lets you specify that the alert will fire only if the event is from the selected database. Keep the default setting of All Databases. The Error Message Contains This Text text box allows you to type a string of characters (up to 100) that restricts the errors that will fire the alert to those whose error text contains that string. If you leave this text box blank, no restriction will apply.
Click the Response tab, shown in Figure 31-20. On this tab, you can specify what action should be taken when this alert occurs. Select the Execute Job check box, and select a job name in the drop-down list to cause that job to be executed when the alert occurs. Clicking New Operator lets you create a new operator to notify. Existing operators appear in the Operators To Notify list. You can specify whether an operator should be notified by e-mail, pager, NET SEND, or a combination of those methods.

Figure 31-20. The Response tab of the New Alert Properties window.

If you specify an operator to be notified by e-mail and also select the Include Alert Error Text In E-mail check box, the error text will be sent to the operator in the alert message. You include the additional text in the e-mail by typing it in the Additional Notification Message To Send text box at the bottom of the tab. The maximum number of additional text characters is 512. Figure 31-20 shows an operator, TestOperator, selected to be notified by e-mail. An additional message is included. Notice also the Delay Between Responses spin boxes. The settings in these spin boxes specify how often the operator will be notified if an alert occurs repeatedly. A setting of 60 minutes means that the operator will be notified only once during any 60-minute period.
To accept the alert and the response you have entered, click Apply. Then click OK to close the window.

Using Enterprise Manager to Create a Performance Condition Alert

Now we'll use Enterprise Manager to create an alert that fires when a certain performance condition occurs. Note that SQLServerAgent polls performance counters at 20-second intervals, so if a peak or a low occurs for only a few seconds between polls, it might not be detected. To create the alert, follow these steps:

In the left pane of Enterprise Manager, expand a server folder, expand the Management folder, and then expand the SQL Server Agent folder. Right-click Alerts and choose New Alert from the shortcut menu. The New Alert Properties window appears (Figure 31-21). Type a name for the alert in the Name text box on the General tab. (Use user_alert for this example.) To specify a performance condition alert, select SQL Server Performance Condition Alert in the Type drop-down list.

Figure 31-21. The General tab of the New Alert Properties window.
In the Performance Condition Alert Definition area, you define the performance condition that will trigger the alert. Select the SQL Server performance object that you want to use as the trigger object in the Object drop-down list, and then select the counter that you want to use in the Counter drop-down list. Set the Alert If Counter option to indicate in which situation the alert should be fired. Finally set a value for the threshold that, if crossed, will trigger the alert. Figure 31-21 shows the settings used to fire an alert when the SQL Server User Connections counter rises above 100.
To finish creating this alert, set the options on the Response tab as described in step 5 of the preceding section, click Apply, and then click OK.

Using T-SQL to Create an Event Alert or a Performance Condition Alert

You can also use T-SQL to create your alerts, but remember that if you create alerts by using Enterprise Manager, you can later generate the T-SQL scripts for those alerts. (To do so, right-click Alerts in the SQL Server Agent folder, point to All Tasks in the shortcut menu, and then choose Generate SQL Script.) You might find using Enterprise Manager to create alerts easier because the T-SQL method requires learning and remembering many optional parameters and their defaults.

To add an alert by using T-SQL, you use the sp_add_alert stored procedure. You use this procedure whether you want to create an event alert or a performance condition alert. The parameter options determine which type of alert is created. The syntax of sp_add_alert is shown here:

 sp_add_alert [@name =] 'name' [, [@message_id =] message_id] [, [@severity =] severity] [, [@enabled =] enabled] [, [@delay_between_responses =] delay_between_responses] [, [@notification_message =] 'notification_message'] [, [@include_event_description_in =] include_event_description_in] [, [@database_name =] 'database'] [, [@event_description_keyword =] 'event_description_keyword_pattern'] [, {[@job_id =] job_id | [@job_name =] 'job_name'}] [, [@raise_snmp_trap =] raise_snmp_trap] [, [@performance_condition =] 'performance_condition'] [, [@category_name =] 'category']

The stored procedures used to modify an alert, view alert information, and delete an alert are sp_update_alert, sp_help_alert, and sp_delete_alert, respectively. Remember, all of these stored procedures are found in the msdb database.

MORE INFO
For more details about the procedures described in this section, look up the procedures in the Books Online index.