Lesson 3: Configuring Alerts

3 4

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.


After this lesson, you will be able to

  • Define alerts in response to SQL Server events
  • Define alerts in response to performance conditions
  • Define responses to alerts
  • View alerts and alert history

Estimated lesson time: 30 minutes


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

Note


To define alerts, you must be a member of the sysadmin server role.

SQL Server Event Alerts

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:

  • Error number—A unique number for each error.
  • Message string—Diagnostic information regarding the cause of the error, including the object name.
  • Severity—Low numbers indicate information messages and high numbers indicate serious errors.
  • State code—Used by Microsoft support engineers to find the source code location for the error.
  • Procedure name—The stored procedure name if the error occurred in a stored procedure.
  • Line number—The line number of a statement in a stored procedure that caused the error.

    SQL Server events and messages (errors) can be written to the application log in a number of ways:

    • Any SQL Server error with a severity number of 19 or greater is automatically logged.
    • Any SQL Server error can be designated as always logged using the sp_altermessage system stored procedure (numerous errors have this designation upon installation).
    • An application can force an error to be logged using the RAISERROR WITH LOG statement or the xp_logevent extended stored procedure.

Note


Make sure to configure the Windows application log to be large enough to hold all SQL Server events without overwriting existing events or running out of space. Also, in order for login events to be logged, Windows auditing must also be enabled.

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.

Performance Conditions

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:

  • Performance object—SQL Server object.
  • Counter—SQL Server counter.
  • Instance—SQL Server instance.
  • Behavior—Value of the counter that triggers the alert. Can be equal to, greater than, or less than a specified value.

Note


If performance condition alerts are not viewable within SQL Server Enterprise Manager, verify that the SQL Server service domain user account has full control permissions on the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \Windows NT\CurrentVersion\Perflib registry key.

Responses

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.

Configuring Alerts

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.

Using the Create Alert Wizard

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.

 figure 13.27 - the create alert wizard welcome screen.

Figure 13.27

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.

Note


Errors with a severity error below 19 are not automatically logged in the application log.

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.

 figure 13.28 - defining the severity level at which the alert will fire.

Figure 13.28

Defining the severity level at which the alert will fire.

 figure 13.29 - choosing specific error types for an alert.

Figure 13.29

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.

 figure 13.30 - searching for error messages.

Figure 13.30

Searching for error messages.

 figure 13.31 - specifying a particular database or specified text for your search.

Figure 13.31

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.

 figure 13.32 - specifying the response to the alert.

Figure 13.32

Specifying the response to the alert.

 figure 13.33 - defining the alert notification message.

Figure 13.33

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.

 figure 13.34 - completing the alert creation process.

Figure 13.34

Completing the alert creation process.

Practice: Creating an Event Alert Using the Create Alert Wizard

In this practice you use the Create Alert Wizard to create an event alert.

To create an event alert using the Create Alert Wizard

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. In the SQL Server Enterprise Manager console tree, verify that your focus is a container within the default instance.
  3. On the Tools menu, click Wizards.

    The Select Wizard dialog box appears.

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

    The Welcome To The Create Alert Wizard page appears.

  5. Click Next.

    The Define The Alert page appears.

  6. Click the Only If This Error Occurs option button, and then type 208 in the Only If This Error Occurs text box.

    Notice that the error description for this error indicates an invalid object name.

  7. Click Next.

    The Specify A Database Or Error Keywords page appears.

  8. In the Database Name drop-down list, click SSEMDB, and then click Next.

    The Define Alert Response page appears.

  9. In the Notify Operator grid, select the Net Send check box for Operator, and then click Next.

    The Define Alert Notification Message page appears.

  10. In the Alert Notification Message To Send To Operator text box, type The object requested does not exist , and then click Next.

    The Completing The Create Alert Wizard window appears.

  11. In the Alert Name text box, type SQL Server Invalid Object Alert , and then click the Finish button.

    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.

  12. Click the Yes button to always invoke the alert.

    A Create Alert Wizard message box appears stating that the alert was created successfully.

  13. Click OK.
  14. In the SQL Server Agent container in the console tree, click Alerts.
  15. Notice that SQL Server Invalid Object Alert appears in the details pane along with the preconfigured alerts.

  16. On the Tools menu, click SQL Query Analyzer.

    SQL Query Analyzer appears with master as the current database.

  17. In the query pane, type SELECT * FROM SSEMDB, and then click the Execute Query button on the toolbar.

    Notice the invalid object name error message in the results pane.

  18. Switch to SQL Server Enterprise Manager.
  19. Right-click SQL Server Invalid Object Alert in the details pane, and then click Refresh Alert.

    Review the Count column. Notice that no alert fired.

  20. Switch to SQL Query Analyzer.
  21. On the toolbar, change the current database to SSEMDB in the database drop-down list, and then click the Execute Query button.

    After a few moments a Messenger Service message box appears displaying information related to this error.

  22. Click OK to close the Message Service message box.
  23. Switch to SQL Server Enterprise Manager.
  24. Right-click SQL Server Invalid Object Alert in the details pane, and then click Refresh Alert.

    Notice that the Count column indicates this alert occurred once and the Last Occurred column displays the date and time that the alert fired.

  25. Leave SQL Server Enterprise Manager and SQL Query Analyzer running.

Using SQL Server Enterprise Manager Directly

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.

 figure 13.35 - creating an alert using sql server enterprise manager directly.

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.

 figure 13.36 - defining a performance condition alert.

Figure 13.36

Defining a performance condition alert.

 figure 13.37 - defining a response to the alert.

Figure 13.37

Defining a response to the alert.

User-Defined Error Messages

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.

 figure 13.38 - adding user-defined error messages.

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.

Practice: Creating a Performance Condition Alert Using SQL Server Enterprise Manager Directly

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

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. In the SQL Server Enterprise Manager console tree, expand the SQL Server Agent container.
  3. Right-click Alerts, and then click New Alert.

    The New Alert Properties - SelfPacedCPU dialog box appears, with the General tab selected.

  4. In the Name text box, type SSEMDB TLog Alert.
  5. Click SQL Server Performance Condition Alert in the Type drop-down list.
  6. Click SQLServer:Databases in the Object drop-down list.
  7. Click Percent Log Used in the Counter drop-down list.
  8. Click SSEMDB in the Instance drop-down list.
  9. Click Rises Above in the Alert If Counter drop-down list.
  10. Type 80 in the Value text box.
  11. Click the Response tab.
  12. Select the Execute Job check box.
  13. Click Backup SSEMDB TLog in the drop-down list.
  14. In the Operators To Notify grid, select the Pager check box for Operator.
  15. In the Additional Notification Message To Send text box, type Transaction Log 80% Full.
  16. Verify that the Delay Between Responses spin box is set to one minute, and then click OK.

    In the details pane, notice that the SSEMDB TLog Alert appears.

  17. Click Start, point to Programs, point to Administrative Tools, and then click Performance.

    The Performance console appears.

  18. On the toolbar, click the Add button.

    The Add Counters dialog box appears.

  19. In the Performance Object drop-down list, click SQLServer:Databases.
  20. In the Select Counters From List box, click Percent Log Used.
  21. In the Select Instances From List box, click SSEMDB.
  22. Click the Add button, and then click the Close button.

    A chart displays the Percent Log Used counter for the SSEMDB database.

  23. Switch to SQL Query Analyzer.
  24. On the toolbar, click the Load SQL Script button.
  25. Click the No button if asked to save changes.

    The Open Query File dialog box appears.

  26. Open TlogOverflow.sql in the C:\SelfPacedSQL\CH_13 folder.

    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.

  27. Click the Execute Query button to execute the TLogOverflow.sql statement.
  28. Switch to the Performance console.

    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.

  29. Click OK.

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

  30. Close the Performance console.
  31. Switch to SQL Query Analyzer, and then click the Cancel Query Execution button on the toolbar.
  32. Close SQL Query Analyzer.
  33. Leave SQL Server Enterprise Manager running.

Using Transact-SQL

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.

Note


Although you can use Transact-SQL system stored procedures to add alerts, use of the SQL Server Enterprise Manager graphical interface is recommended.

Reviewing Alerts and Alert History

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.

Note


Because SQL Server Enterprise Manager is a client application, information displayed must be refreshed to view the most recent information (such as last occurred and count information).

 figure 13.39 - viewing alert information.

Figure 13.39

Viewing alert information.

Alert Properties

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.

Transact-SQL

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.

Lesson Summary

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.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net