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:
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.
SQL Performance Monitor starts.
The Add to Alert dialog box appears.
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.
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.
sqlalrtr -E error_number [-S server_name] [-P password]
[-D database_name] [-V severity] [-T ]
where
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.
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.
The default is Every Time.
The Performance Monitor alert is added.
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.
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.