Using SQL Server Agent, you can define alerts and configure responses to alerts. In this lesson, you learn how to create alerts to different types of events, including SQL Server 2000 error messages, user-defined error messages, and performance conditions. You also learn to configure operators to be notified and jobs to be run in response to alerts.
Database administrators define alerts to provide event and performance condition notification and to execute jobs in response to specific SQL Server events or performance conditions. For example, whenever the transaction log becomes 80 percent full, an alert can be configured to fire that executes a job to back up and truncate the transaction log (and to notify an administrator of its success or failure).
SQL Server Agent monitors the Windows application log and compares each SQL Server event logged with the alerts that have been defined. If a match is found, an event alert fires. SQL Server Agent can also monitor specific SQL Server performance object counters and fire a performance condition alert when the value for the counter is less than, equal to, or greater than a defined threshold.
The sysmessages table in the master database contains system error messages. User-defined event messages can also be added to the sysmessages table using SQL Server Enterprise Manager or the sp_addmessage system stored procedure. All user-defined event messages are numbered 50,000 or greater. SQL Server events and messages (errors) have the following attributes:
SQL Server events and messages (errors) can be written to the application log in a number of ways:
You define an event alert based on a SQL Server event written to the application log and meeting specified conditions (such as severity level, error number, or containing specified text). SQL Server 2000 includes a number of preconfigured event alerts you can modify and use, or you can create your own.
SQL Server 2000 provides objects and counters that are used by Windows 2000 System Monitor (and Windows NT 4.0 Performance Monitor). These same objects and counters can be used by SQL Server Enterprise Manager to define a performance condition alert. To define a performance condition alert, you define the following:
When an event alert or a performance condition alert fires, one or more operators can be notified using e-mail, pager, or NET SEND. A custom notification message can be added to the alert notification along with the text of the error. A specified job can also be executed in response to the alert.
You can create alerts using the Create Alert Wizard or directly by using SQL Server Enterprise Manager. You can also create alerts with Transact-SQL system stored procedures.
The SQL Server Enterprise Manager wizards are available from the Tools menu, and are also available from any taskpad view. The Create Alert Wizard is a simple way to create an event alert. Figure 13.27 shows the Welcome To The Create Alert Wizard page.
The Create Alert Wizard welcome screen.
In the Define The Alert page, you can click the For Any Error Of Severity option button to specify that an alert fire if the severity level of the SQL Server event equals or exceeds the severity level you specify in the For Any Error Of Severity drop-down list. See Figure 13.28.
You can also click the Only If This Error Occurs option button to specify that an alert fire if a specific error occurs. You can browse available error numbers and messages to select the error number by clicking the Only If This Error Occurs ellipsis button. Clicking this button displays the Manage SQL Server Messages dialog box, as illustrated in Figure 13.29.
Defining the severity level at which the alert will fire.
Choosing specific error types for an alert.
Notice that you can search for error messages based on text, error number, and severity level. Notice that you can also limit your search to include only logged messages and/or user-defined messages. After you have defined your search parameters, you click the Find button to begin the search. Figure 13.30 illustrates the results of a search.
Next, in the Specify A Database Or Error Keywords page, you can specify that the event must occur in a particular database or contain specified text. See Figure 13.31.
Searching for error messages.
Specifying a particular database or specified text for your search.
Next, in the Define Alert Response page, you specify the response to the alert. You can select from among existing operators and specify the type of notification. You can also select a job to execute, including the option to create a new job on the fly. See Figure 13.32.
Next, in the Define Alert Notification Message page, you can specify the text of the message that will be sent to the operator as part of the alert notification message. You can also choose whether to include the text of the error message in the message to the operator. See Figure 13.33.
Specifying the response to the alert.
Defining the alert notification message.
Finally, in the Completing The Create Alert Wizard page, you are given the opportunity to specify a name for the alert and to review the selections you have made before you actually create the alert. You can click the Back button to change any parameter you want to change. Click the Finish button to create the alert. See Figure 13.34.
Completing the alert creation process.
In this practice you use the Create Alert Wizard to create an event alert.
To create an event alert using the Create Alert Wizard
The Select Wizard dialog box appears.
The Welcome To The Create Alert Wizard page appears.
The Define The Alert page appears.
Notice that the error description for this error indicates an invalid object name.
The Specify A Database Or Error Keywords page appears.
The Define Alert Response page appears.
The Define Alert Notification Message page appears.
The Completing The Create Alert Wizard window appears.
A Create Alert Wizard dialog box appears stating that occurrences of error 208 will not invoke this alert because error 208 is not logged by default. The wizard then asks whether you want to have the error always invoke this alert.
A Create Alert Wizard message box appears stating that the alert was created successfully.
Notice that SQL Server Invalid Object Alert appears in the details pane along with the preconfigured alerts.
SQL Query Analyzer appears with master as the current database.
Notice the invalid object name error message in the results pane.
Review the Count column. Notice that no alert fired.
After a few moments a Messenger Service message box appears displaying information related to this error.
Notice that the Count column indicates this alert occurred once and the Last Occurred column displays the date and time that the alert fired.
To create an alert using SQL Server Enterprise Manager directly, you can either right-click SQL Server Agent in the Management container, point to New and then click Alert, or you can right-click Alerts in the SQL Server Agent container and then click New Alert. The New Alert Properties dialog box appears as illustrated in Figure 13.35.
Creating an alert using SQL Server Enterprise Manager directly.
In the General tab, notice that you can select either an event alert or a performance condition alert in the Type drop-down list. The information required to create an event alert is the same as covered earlier in this chapter for the Create Alert Wizard. If you select a performance condition alert, you can define the performance condition that will cause an alert to fire. For example, you can define an alert that will fire whenever the percentage of the transaction log used for the SSEMDB database rises above 80 percent. See Figure 13.36.
Next, click the Response tab to define a response to the alert. When you click the Execute Job check box, you can select from an existing job, or create one on the fly. Click the ellipsis button to view or modify the details of any job for which you have sufficient permissions. You can select operators to notify, create new operators, choose to include the error text in the notification, and provide a message to include in the notification. You can also configure a delay between responses. Use this delay to prevent the firing of multiple alerts from overwhelming your system. For example, if an alert fires and causes a job to execute to resolve the cause of the alert, delay the firing of a second alert until the job has an opportunity to complete its task. See Figure 13.37.
Defining a performance condition alert.
Defining a response to the alert.
To add user-defined error messages using SQL Server Enterprise Manager, click Manage SQL Server Messages on the Tools menu, click the Messages tab, and then click the New button to display the New SQL Server Message dialog box. In this dialog box, you select the error number you want to use, define its severity level, provide the text for the message, specify the language, and specify whether it will be automatically written to the application log whenever raised. The message string can contain substitution variables and arguments. See Figure 13.38.
Adding user-defined error messages.
You can have a user-defined error message raised when a particular transaction occurs, which can then trigger a defined alert response. For example, a stored procedure can be created that is used to add new customers. Within the stored procedure, a RAISERROR statement can raise a user-defined error and pass variables to the error message providing the name of the new customer and the user adding the customer. The defined alert can then notify a database administrator that a new customer was added.
In this practice you create a performance condition alert using use SQL Server Enterprise Manager directly.
To create a performance condition alert using SQL Server Enterprise Manager directly
The New Alert Properties - SelfPacedCPU dialog box appears, with the General tab selected.
In the details pane, notice that the SSEMDB TLog Alert appears.
The Performance console appears.
The Add Counters dialog box appears.
A chart displays the Percent Log Used counter for the SSEMDB database.
The Open Query File dialog box appears.
A Transact-SQL script appears, which will shrink the transaction log file and then will continually update the ContactName column in the Customer table in the SSEMDB database. Notice that a wait of 10 milliseconds has been specified. This will prevent the transaction log from filling so quickly that the backup job does not have time to finish the backup before the transaction log file automatically grows. Increase this wait time on fast computers and reduce it for slow computers.
Notice that the transaction log begins to fill up. Several moments after the Percent Log Used counter exceeds 80 percent, a Messenger Service message box appears. Notice the details of the error message. In particular, notice that the network pop-up is delivered to FailSafe. This occurs because Operator, who is designated to receive pager notification when this alert fires, is off duty.
After a few more moments, notice that the chart displaying the Percent Log Used counter in the Performance console indicates that the job has executed (the transaction log was truncated).
You can also define alerts using the sp_add_alert, sp_update_alert, and sp_add_notification system stored procedures. You must be a member of the sysadmin server role to execute these system stored procedures. You can create and manage user-defined error messages using the sp_addmessage and sp_altermessage system stored procedures. You must be a member of the sysadmin or serveradmin server roles to execute these system stored procedures.
The details pane of the Alerts container in SQL Server Enterprise Manager displays information regarding all alerts for the SQL Server 2000 instance. See Figure 13.39. You must be a member of the sysadmin server role to view alerts.
Information regarding each alert is displayed in columns. Click on a column heading to sort the alerts based on the column. Notice the Enabled column. If you are troubleshooting an alert that does not fire when it should, verify that it is enabled. If an alert is firing too frequently, you might need to disable it before you modify its properties. Information is displayed regarding the last time the alert fired, the notifications that were sent, and how many times the alert has fired since it was last reset.
Viewing alert information.
Right-click an alert, and then click Properties (or double-click the alert) to display the alert's Properties dialog box, where you can view the details on the alert. Click the Reset Count button to reset the counter displaying the number of times the alert has fired.
You can also view alert properties using the sp_help_alert system stored procedure. You must be a member of the sysadmin server role to execute this system stored procedure.
Event alerts can be defined to respond to SQL Server errors that are logged in the Windows application log. Severe errors are always logged in the Event Viewer application log and you can configure any error to be logged. Performance condition alerts can be defined to respond to performance object counter values above, below, or equal to specified values. Responses to alerts can be the execution of a specified job and/or notification of one or more operators.