MS SQL Basics Tutorial: SQL alerts

An alert is defined in SQL Server through the alert manager, and is automatically alerted when certain events occur. When an alert is triggered, notify the operator by e-mail or pager, allowing the operator to understand what is happening in the system, such as a lack of database space or a full transaction log. Error messages generated by SQL Server, events are recorded in the Windows NT Application log, SQL Server reads the logs and compares the events that have already been defined, and if they match, an alert is triggered.

Use the alert Manager in SQL Server to create two types of alerts: Event alerts and performance status alerts.

17.4.1 Create Event Alerts

The steps to create an event alert using SQL Server Enterprise Manager are:

(1) Start SQL Server Enterprise Manager, log on to the specified server, click Management in sequence, and then "+" on the left of the SQL Server Agent (if the SQL Server Agent does not start, start).

(2) Right-click the Alerts icon and select the new Alert option in the pop-up menu to open the New Alert Properties dialog box. Select the General tab from the example shown in Figure 17-14. The meaning of each of these options is;

Name: The names of the alerts, which are no longer than 128 characters; type: Types of alerts, SQL Server event alert indicates an alert for the type of event, SQL Serverperformance condition alert indicates a performance condition alert; Er ROR Number: An error that triggers an alert, which can only be used in an event alert; Severity: Indicates the level of the error, with a value ranging from 1 to 25. Can only be used in event alerts. (3) Select the Error Number radio button and click the Box button to the right to open the Manage SQL Server Messages dialog box and select the Search tab page. As shown in Figure 17-15.

In the dialog box, you can enter a string in message Text Contains, and clicking the Find button displays all the error messages that include the string on the Messages label page.

(4) Select the Message tab and click the "New" button to eject the new SQL Server message dialog box, as shown in Figure 17-16.

Message: Indicates the text of the error messages with a maximum length of 255 characters;

Always write to Windows NT Event log: Indicates that the message is written to the Windows NT application log, and you must select this check box if you want the SQL Server Agent to monitor alerts.

(5) Click OK to return to the new Alert Properties dialog box and select the Response tab page. As shown in Figure 17-17.

Execute job: The job that is executed when an alert occurs; In the table under operator to notify: shows which operators to send the alert to, and defines how (mail, paging) is routed; Include alert error text in: Indicates which (Mail, paging, network transfer) notification; Additional notification message to send to operator: Additional messages to the operator; Delay between responses for a Recurrin G Alert: A time interval that indicates repeated alerts for two consecutive responses.

17.4.2 Create performance Alerts

Performance alerts are when a performance index exceeds a predefined performance threshold, the performance alert is triggered, and the appropriate warning message is notified to the administrator by e-mail or paging defined in the performance alert.

The creation steps for performance alerts are almost identical to the creation steps of an event alert, and the main differences are as follows:

When you open the new Alert Properties dialog box, select the General tab and select SQL Server performance Condition Alert in the Drop-down list next to type, as shown in Figure 17-18.

Object: Windows NT Performance Monitor object; Counter: Represents Windows NT Performance Monitor counter; Instance: instance specified by counter; Alert if Counter: Threshold value of the condition; value: The Threshold of the alert.

Other settings refer to the creation of event alerts.

Summary of this chapter

The SQL Server Agent is a powerful tool for automating administrative tasks and provides a convenient and simplified database management environment with the help of Job planner and alert management tools, dramatically reducing the workload of management tasks and improving management efficiency.

See the full set of "MS SQL Basics Tutorials"