Lesson 3: Creating Alerts | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

SQL Server allows you to respond to potential problems by creating alerts to respond to SQL Server errors, user-defined errors, or SQL Server performance conditions. You can also create a fail-safe operator in the event that a pager notification fails to contact an operator. This lesson describes how to perform these tasks.

After this lesson, you will be able to

Create alerts to respond to SQL Server 7 errors
Use SQL Server performance condition alerts to notify a system or database administrator (also known as an operator) of potential problems before they occur
Configure SQL Server event forwarding
Assign a fail-safe operator

Estimated lesson time: 90 minutes

Using Alerts to Respond to Potential Problems

Alerts respond to SQL Server or user-defined errors (events) that have been written to the Windows NT application log. SQL Server errors are raised in response to predefined problems, such as insufficient user permissions to modify a table or the transaction log becoming full. To raise user-defined messages, the database application (typically a stored procedure or trigger) must call the RAISERROR statement.

Exercise: Creating an Alert with the Create Alert Wizard

In this exercise, you will use the Create Alert Wizard to create an alert that is defined to send e-mail and net send notifications when an error with severity level 17 occurs.

To create an alert by using the Create Alert Wizard

Open SQL Server Enterprise Manager, and expand your server.

On the Tools menu, click Wizards.

Expand Management, and then double-click Create Alert Wizard.

Create an alert using the information in the following table. Accept defaults for any items that are not listed.

Option	Value
For any error of severity	017 — Insufficient Resources
Database name	(All Databases)
Job to execute	(No job)
Notify operator(s)	Admin — E-mail and Net send
Include error message text in	E-mail and Net send
Alert notification message to send to operator	My first alert
Alert name	Severity level 17 errors

In the console tree, expand Management, expand SQL Server Agent, and then click the Alerts icon.

In the details pane, verify that the alert named Severity Level 17 Errors was created.

Right-click the alert, and then click Properties to review the alert definition.

What additional responses to the alert can be defined that were not included as part of the wizard?

Answer

Creating Alerts to Respond to SQL Server Errors

You can create alerts that fire in response to specific SQL Server errors or that fire in response to all errors of a specific severity level.

TIP
SQL Server has a number of predefined demonstration alerts. Since there are no operators assigned to these alerts, they do nothing. You should assign operators to these alerts and give them names that are more meaningful or replace them with alerts of your own.

Defining Alerts Based on SQL Server Error Numbers

You can use SQL Server Enterprise Manager, the Create Alert Wizard, or the sp_add_alert system stored procedure to define a new alert. The alert definition is stored in the msdb..sysalerts system table. This table is maintained in cache to improve performance. When you define an alert for a SQL Server error number, consider the following facts and guidelines:

Alerts fire only for errors that are written to the Windows NT application log.

You can define alerts based on SQL Server system or user-defined error numbers that are stored in the master..sysmessages system table.

You can define more than one alert for a SQL Server error number. However, each alert must be limited to a specific database or must apply to all databases.

For example, to respond to error number 9002 in both the payroll and customer databases, you could create two separate alerts. Alternatively, you could create one alert to respond to error number 9002 in all databases.

When you create an alert that applies to all databases, ensure that the error message provides a sufficiently detailed explanation, typically giving the database name in the message. If the message does not give a detailed explanation, create separate alerts for each database.

You can make the alert more specific or selective by specifying text that must be contained in the error message for the alert to fire.

For example, if you create an alert for error 18456, the error message includes the name of a user who fails to log on. You could specify the text "Login failed for user 'sa'" so that the alert occurs only when someone is trying to log on as sa.

Defining Alerts Based on Error Severity Levels

The following table provides a summary of the SQL Server error severity levels.

Severity level	Description	Can be corrected by	Written to NT application log
0 - 10	Informational messages; these are not errors	N/A	Optional
11 - 16	User errors	User	Optional
17	Insufficient resource errors	Administrator, possibly database owner	Optional
18	Nonfatal internal error	Administrator	Optional
19	Nonconfigurable resource error	Administrator	Yes
20 - 25	Fatal errors	Administrator	Yes

When you define an alert for an error severity level as a condition, consider the following facts and guidelines:

SQL Server errors with severity levels 19 through 25 are automatically written to the Windows NT application log.

Severity levels 20 through 25 are fatal errors, meaning that the code in which the error occurs is terminated and the user is disconnected. You should always define an operator to be notified when these SQL Server errors occur.

You can create an alert to be fired when an error of a specific severity level occurs on all databases or on a particular database.

You can make the alert more specific or selective by specifying text that must be contained in the error message. For example, you can create an alert to notify you when a severity level 17 error that includes the string "log" in the error message occurs in any database.

To ensure that notifications for severe errors are received, it is recommended that you configure alerts for severe errors to send notifications to groups rather than to individual users.

Creating Alerts Based on User-Defined Error Messages

You can create alerts based on user-defined (customized) error messages. These can be defined for individual database applications, allowing you to define sophisticated solutions to avoid potential problems before they occur.

For example, you could create a user-defined error message to be raised from an update trigger on an inventory table. When a column in the inventory table is updated, indicating that inventory levels have fallen below 25 percent for a particular product, the trigger will raise your user-defined error. You could then define an alert for the error message that executes a job to reorder inventory and sends an e-mail message to the purchasing agent.

Before creating an alert for a user-defined error message, you must first create the error message. The programmer must raise the error from the database application, using the RAISERROR statement; SQL Server will not raise the error.

Creating an Error Message

To create user-defined error messages, you can use SQL Server Enterprise Manager or the sp_addmessage system stored procedure. When you create user-defined error messages, consider the following facts:

The numbers assigned to user-defined error messages must be greater than 50000. Error numbers less than 50000 are reserved for predefined SQL Server errors.

Error messages can include parameters to report specific details such as a database or user name.

SQL Server error messages are displayed in the language that is selected during Setup. If you administer a multiple-language SQL Server environment, you can create user-defined messages for other languages.

You must write the error message to the Windows NT application log if you plan to fire an alert on the message.

Example of an Alert Based on a User-Defined Error Message

An account manager wants to be notified by e-mail any time a customer is removed from the database. She also wants to know the name of the employee who deleted the customer in the event that subsequent action is necessary.

The following steps give the sequence of events that occur in this scenario when the alert fires. Figure 13.7 illustrates the scenario.

Eva Corets, a customer service representative, removes customer van Dam from the customers table. The removecustomer stored procedure is executed, which raises error number 50099.

The error (event) is written to the Windows NT application log.

SQL Server Agent is notified that an event has occurred and then reads the Windows NT application log.

SQL Server Agent compares the error to defined alerts in the msdb..sysalerts system table.

SQL Server Agent processes the alert that was found for the 50099 error. Notifications defined in the msdb..sysnotifications system table are sent to operators based on details defined in the msdb..sysoperators system table.

click to view at full size.

Figure 13.7 Example of an alert based on a user-defined error message

Exercise: Creating a User-Defined Error Message

In this exercise, you will use SQL Server Enterprise Manager to create a user-defined error message that occurs when the number of stock units for a particular product reaches the reorder level.

To create a user-defined error message

Right-click your server, click All Tasks, and then click Manage SQL Server Messages.

In the Manage Server Messages dialog box, click the Messages tab.

NOTE
The Messages tab of the Manage SQL Server Messages dialog box will always be empty when you display it. If you want to see messages, you must first enter search criteria on the Search tab and click Find. This will populate the Messages tab with a list of messages that match your search criteria. For this exercise, you are adding a message, so it is not necessary to enter search criteria.

Click New.

Note that the error number 50001 has been generated for your new error message. Do not change this number; it is required to be 50001 in a later exercise.

In the New SQL Server Message dialog box, in the Severity list, click Severity Level 009 - User Defined.

In the Message text box, enter the following event message:

The units in stock for %s has reached %d. Please reorder.

Check the Always Write To Windows NT Eventlog option to write the event message to the Windows NT application log.

Click OK to close the dialog box and add your new error message.

Click OK to close the Manage SQL Server Messages dialog box.

Exercise: Creating an Alert for the User-Defined Error Message

In this exercise, you will create an alert that sends an e-mail message to the warehouse manager when your new user-defined error message occurs.

To create an alert for the user-defined error message

Expand your server, expand Management, expand SQL Server Agent, right-click Alerts, and then click New Alert.

In the New Alert Properties dialog box, in the Name box, enter Reorder Inventory.

Click Error Number and type 50001 in the error number field, the error number of your user-defined error message.

Note that as you type, the message next to Error Number changes from (Not a valid error number) to the error message when the error number is found in the sysmessages table.

You can search for errors by message text, error number, or severity by clicking browse (…) to open the Manage SQL Server Messages dialog box.

In the Database Name list, click StudyNwind to restrict the alert to a specific database.

Click the Response tab.

Check E-mail and Net Send for the operator Admin.

Click OK to close the dialog box and add the new alert.

Verify that the alert was created. The Reorder Inventory alert should appear in the details pane.

Exercise: Raising the User-Defined Error Message

In this exercise, you will use SQL Server Query Analyzer to execute a stored procedure in the StudyNwind database that will raise error 50001 to test that the Reorder Inventory alert works as expected.

To raise a user-defined error message

Open SQL Server Query Analyzer and log on to the (local) server with Microsoft Windows NT authentication. Your account is a member of the Windows NT Administrators group, which is automatically mapped to the SQL Server sysadmin role.

Execute the reorder stored procedure by supplying any valid product ID value. For example,

 USE StudyNwind EXEC reorder @prodid = 2

Did you receive the defined notifications in response to the alert?

Answer

Configuring Event Forwarding

You can configure SQL Server Agent to forward unhandled event messages or all event messages to another SQL Server. You can specify that only events above a certain severity level be forwarded. The other SQL Server handles the events based on its own alert definitions. The name of the server on which the error occurred will be reported in the alert notification.

In a multiserver environment, this means that you need to define alerts on only one server. You may want to forward events to a server that has less traffic than other servers.

For example, you can configure errors with severity levels of 18 or above to be forwarded to the Accounting server, as shown in Figure 13.8. If an error occurs on your server with severity level 19, the event is automatically forwarded to the Accounting server to address the problem.

Figure 13.8 The Advanced tab of the SQL Server Agent Properties dialog box, showing the SQL Server Event Forwarding options

IMPORTANT
Event forwarding is available only when SQL Server is installed on Windows NT, not Windows 95 or Windows 98.

Responding to Performance Condition Alerts

You can create alerts to respond to SQL Server performance conditions defined by the objects and counters used in Windows NT Performance Monitor. An alert is fired when the monitored value exceeds, equals, or falls below a defined limit. Such alerts allow you to proactively avert problems and keep your server and databases running smoothly.

For example, you can create a performance condition alert to be fired when the transaction log in the Northwind database has exceeded 75 percent of its capacity. Figure 13.9 illustrates such an alert.

click to view at full size.

Figure 13.9 Example of an alert based on a performance condition

You can create SQL Server performance condition alerts based on most of the SQL Server Performance Monitor objects. Examples of the measures on which you can base alerts include

The memory buffers used by SQL Server, such as free memory and the buffer cache hit ratio

The number of index searches or the number of pages that are allocated to indexes and data

The amount of SQL Server cache used to store objects such as stored procedures, triggers, and query plans

The amount of free log space available or the number of active transactions in the database

Lock time-outs and deadlocks

Custom stored procedures or any Transact-SQL statements that return a value to be monitored

Windows NT Performance Monitor does not need to be running on your SQL Server for you to use performance condition alerts.

NOTE
Previous versions of SQL Server required Windows NT Performance Monitor to be running in order to use performance-based alerts. A utility called sqlalrtr had to be executed from Windows NT Performance Monitor to report performance alerts. This is not necessary in SQL Server 7.

Practice: Creating a Performance Condition Alert

In these exercises, you will execute a script that creates a multistep job to back up the StudyNwind transaction log. Next, you will use SQL Server Enterprise Manager to create a performance condition alert based on the Percent Log Used counter that notifies you and executes the backup job when the StudyNwind database transaction log has reached 60 percent capacity. Finally, you will verify that the condition alert works as expected.

Exercise 1: Creating a Job to Back Up the Transaction Log

In this exercise, you will execute a script that creates a multistep job to back up the StudyNwind transaction log.

To execute a script that creates a job to back up the StudyNwind transaction log

Switch to SQL Server Query Analyzer, and open a new query window.

Open C:\Sqladmin\Exercise\Ch13\Makejob.sql, and then execute it.

This script creates a multistep job called Backup StudyNwind Log Alert that will back up the StudyNwind transaction log. Job steps also include contingencies to truncate the transaction log and back up the database if the BACKUP LOG statement fails.

NOTE
As this script is executing, you will receive warning messages that nonexistent steps are referenced. This is normal behavior. When each job step is created, actions to take upon success or failure are specified. The first few job steps reference steps that have not yet been created.

Switch to SQL Server Enterprise Manager, expand your server, and then expand Management. Right-click SQL Server Agent, and click Refresh.

Expand SQL Server Agent and then click Jobs. The Backup StudyNwind Log Alert job should appear in the details pane.

Double-click the Backup StudyNwind Log Alert job to review the job properties.

Exercise 2: Creating a Performance Condition Alert for the StudyNwind Transaction Log

In this exercise, you will create a performance condition alert that fires when the Percent Log Used counter rises above 60 percent for the StudyNwind database.

To create a performance condition alert

Open SQL Server Enterprise Manager.

Expand Management, and then expand SQL Server Agent. Right-click Alerts and click New Alert.

Type StudyNwind Log 60% Full in Name.

In Type, select SQL Server Performance Condition Alert.

Use the values in the following table to create the Performance alert condition definition.

Option	Value
Object	SQLServer:Databases
Counter	Percent Log Used
Instance	StudyNwind
Alert if counter	rises above
Value	60

Click the Response tab and define the response to the alert with the information in the following table. Accept the defaults for any items that are not listed.

Option	Value
Execute job	Backup StudyNwind Log Alert
Operators to notify	Admin by E-mail and Net send
Delay between responses	0 minutes, 0 seconds

Click OK to close the dialog box and add the new alert.

Exercise 3: Testing the Performance Condition Alert

In this exercise, you will review and execute a script that generates activity in the StudyNwind database to fill the transaction log. Then you will verify that SQL Server Agent raised the alert and executed the job as defined.

To test the performance condition alert

In Windows NT Explorer, find and double-click the C:\Sqladmin\Exercise\Ch13 \Watchlog.pmc file. This opens the Windows NT Performance Monitor and displays two counters. The counters show the size of the StudyNwind transaction log (green) and the percentage of the transaction log currently in use (red). The scale for the size of the transaction log is x100 KB. The Percent Log Used counter always shows the amount of space used in the transaction log as a percentage of the current size of the transaction log, so the percentage will drop if the log grows. The percentage is not determined by size settings for the log such as maximum size, only by the current size.

Switch to SQL Server Query Analyzer, open C:\Sqladmin\Exercise\Ch13 \Fulltlog.sql, review its contents, and then execute it.

This script simply generates activity in the StudyNwind database that fills the transaction log.

Switch to Performance Monitor; you will see the transaction log filling up and possibly see the transaction log size grow.

When the alert is raised, you will receive a net send message. Shortly after the alert message, you will receive another net send message indicating that the transaction log has been backed up; the percentage of the transaction log in use will then drop. Allow the alert to fire two or three times, and then stop the execution of the script.

IMPORTANT
Do not allow the script to run for too long, or the transaction log backup file will grow very large.

Open Inbox to confirm that you received an e-mail message notifying you of an error.

Switch to SQL Server Enterprise Manager.

Review the job history for the Backup StudyNwind Log Alert job.

The job history should display the most recent date and time that the job was executed successfully.

Review the alert history of StudyNwind Log 60% Full by right-clicking the alert and selecting Properties. The history can be found at the bottom of the General tab. It should show the most recent date and time that the alert was raised.

Assigning a Fail-Safe Operator

You can assign a fail-safe operator to respond to an alert when pager notifications to defined operators fail. For example, if an operator is off-duty when an alert fires, the fail-safe operator will be contacted.

A fail-safe operator is notified when

The operator(s) assigned to the alert cannot be paged

SQL Server Agent cannot access the system tables in msdb

When you assign a fail-safe operator, consider the following facts:

The fail-safe operator information is cached. Therefore, even if SQL Server Agent stops unexpectedly, the fail-safe operator can still be notified.

You can have only one fail-safe operator.

For safety reasons, you cannot delete an operator that has been designated as the fail-safe operator. You must either designate a new fail-safe operator or remove the fail-safe operator assignment and then delete the operator.

Lesson Summary

Alerts make it possible to created automated responses to errors and other conditions on SQL Server. Alerts can be created based on error numbers, error severity levels, user-defined error messages, and performance conditions. In a large environment, multiple SQL Servers can forward events for processing on a single server, centralizing the administration of the alert responses. It is important that somebody always be notified when certain alerts occur; you can configure a fail-safe operator that can be notified if the normal operators cannot be notified.