Generating Alerts from Performance Monitor Thresholds

Using the event logging utility, SQLALRTR.EXE, you can generate a SQL Server alert when a selected SQL Performance Monitor threshold is exceeded. To do this, you:

Set a SQL Performance Monitor alert on a SQL Server counter.
Configure SQL Performance Monitor to run SQLALRTR.EXE when the alert threshold is exceeded, thus writing a SQL Server event to the Windows NT application log.
Use SQL Enterprise Manager to define an alert on that event.

When the specified SQL Performance Monitor threshold is exceeded, a Performance Monitor alert is generated, and SQLALRTR.EXE runs. It starts isql, which logs on to the server and issues a RAISERROR WITH LOG command. This causes an event to be written to the Windows NT application log. SQL Executive reads that event, and the alerts engine fires a SQL Server alert.

Note that the SQLALRTR.EXE utility is necessary because isql cannot be used directly with Performance Monitor. Performance Monitor passes the Windows NT alert information as Windows NT command line parameters, and isql will not work correctly in that context.

To generate an alert from a Performance Monitor threshold, you actually define two separate alerts: a SQL Performance Monitor alert and a SQL Server alert.

To generate an alert from a Performance Monitor threshold

From the Microsoft SQL Server 6.0 program group, choose the SQL Performance Monitor icon.

SQL Performance Monitor starts.
From the View menu, choose Alert, and then from the Edit menu, choose Add to Alert.
The Add to Alert dialog box appears.
From the Object box, select a SQL Server object, and then from the Counter box, select a counter.
For a brief description of the selected counter, choose the Explain button. For more information about each SQL Server object and counter, see Chapter 19, Monitoring Server Activity and Performance.
In the Alert If box, select either the Over or Under option, and then type a threshold (a number) in the Alert If box.
A Performance Monitor alert will occur when the statistic returned for that counter exceeds (if you selected Over) or falls below (if you selected Under) the threshold.
Enter a SQLALRTR.EXE command in the Run Program on Alert box. Type:
sqlalrtr -E error_number [-S server_name] [-P password]
[-D database_name] [-V severity] [-T ]
where

-E error_number

Specifies an error number that will be associated with this event, and error_number is the number. For information on SQL Server error numbers, see Part 8, Troubleshooting.

-S server_name

Specifies the server on which the alert will be raised, with server_name the server name.

-P password

Specifies that the command runs in the context of the SA account, and password is the SA password on the server.
To avoid the need to supply the SA password on the command line (a possible security exposure), configure the server to run integrated security; or use the -T option instead.
If you specify neither -P nor -T, then -T is assumed.

-D database_name

Specifies that the alert will be defined only for that database, with database_name the database name.

-V severity

Specifies the severity level of the error, with severity a number between 1 and 25. For information on SQL Server error severity levels, see Part 8, Troubleshooting.

-T

Specifies a trusted connection. To use this, the Windows NT user account used by Performance Monitor must be granted (or be a member of a group that has been granted) SA privileges on the target server. Grant SA privileges using SQL Security Manager.

For example, to raise error "123456" on the local server:
```
D:\SQL60\BINN\SQLALRTR /E123456
```
Note The SQLALRTR.EXE parameters (-E, -S, -P, -D, -V, and -T) are case-sensitive and must be entered as capital letters.
In the Run Program on Alert box, choose either the First Time option or the Every Time option.
The default is Every Time.
In the Add to Alert dialog box, choose Add.
The Performance Monitor alert is added.
Optionally, repeat steps 3 through 7 to add additional Performance Monitor alerts.
Choose Done.
The Alerts window of SQL Performance Monitor appears. The Alert Legend displays a list of the defined Performance Monitor alerts. The Alert Log displays a list of alert occurrences.
Now that the SQL Performance Monitor alert has been defined, use SQL Enterprise Manager to create an associated SQL Server alert.
Generate the SQL Server alert on the error_number or severity that was provided with the SQLALRTR.EXE command in step 5. For instructions on creating a SQL Server alert, see "Creating an Alert," earlier in this chapter.

Once you have defined an alert, SQL Performance Monitor and the SQL Executive service must both be running for the alert to be generated.

For more information about errors and severity levels, see Part 8, Troubleshooting. For more information about SQL Server performance objects and counters, see Chapter 19, Monitoring Server Activity and Performance. For more information about using SQL Performance Monitor, see its online Help. For more information about SQLALRTR.EXE, see the Microsoft SQL Server Transact-SQL Reference.

Note You cannot pass parameters to a SQL Server error that is raised using the SQLALRTR.EXE utility. For example, if you run the command sqlalrtr /E123456, then the message for error 123456 should not take parameters. For more information on defining and using error messages which take parameters, see the discussion of RAISERROR in the Microsoft SQL Server Transact-SQL Reference.