Under conditions of rapidly occurring alerts, the delay between the event appearing in the Windows NT application log and the response by SQL Executive may increase. (Normally, it is only a few seconds.) This can occur because the alerts engine has a top response speed governed by its architecture¾SQL Executive competes with SQL Server for process bandwidth, and is designed to be economical with threads.
The rate at which SQL Executive can process alerts is also dependent upon the complexity of the alert response¾that is, the number of notifications. It is a good idea to send notifications to as few operators as possible. One technique is to send notifications to one group email address rather than notifying several individual operators.
If the rate of alert occurrence exceeds the rate at which SQL Executive can process alerts, a backlog of alerts will develop. If backlogs develop, increase the value for the Delay Between Responses for Recurring Alerts option for each alert. For information about setting this value, see Creating an Alert, earlier in this chapter.
If an alert is not firing, first check the following:
If the alert's "Last Occurred" and "Count" values are changing, then that alert is occurring but there may be a problem with the response firing. If you have defined email or pager notifications to occur, test the email and/or pager addresses of the assigned operators by using the Test button (next to the address boxes in the Edit Operators dialog box). If a pager notification is not being received, check that the operator's pager notification schedule is set for the necessary days and hours.
If you turn off Windows NT event logging (by using the Server Options feature of SQL Setup or SQL Enterprise Manager as described in Chapter 3, Configuring Servers), then the alerts engine will not be able to pick up events from the Windows NT application log, and alerts will not be generated. Other SQL Executive functions will operate normally, and SQL Executive will continue to write any of its own errors to the Windows NT application log.
The Log Settings options for the Windows NT Event Viewer application affect the ability of the alerts engine to detect SQL Server events and issue alerts.
When the Do Not Overwrite Events (Clear Log Manually) option is selected, be sure to increase the Maximum Log Size setting to at least 2048KB.
Important If you select the Do Not Overwrite Events (Clear Log Manually) option, you must periodically clear the application log manually. If you do not do this, then when the application log fills no more events can be added to the log, and the SQL Server alerts engine will be unable to generate alerts on subsequent (unlogged) events.
For information about setting the Event Viewer options, see its online Help.
For low-capacity alphanumeric paging systems (for example, those limited to 64 characters per page), you can prevent the notification for a single alert from being split over multiple pages by selecting the option Include Body of Email in Notification Page. This option appears in the Pager-Email tab of the Alert Engine Options dialog box.
The xp_logevent extended stored procedure always has a database context of master, so you cannot filter on database name (other than master) for alerts that will be invoked using xp_logevent.
For information about xp_logevent, see the Microsoft SQL Server Transact-SQL Reference.
When using internet and other specially addressed emails (including pager emails), it may be necessary to suppress the name checking features (that is, the check for existence of the email name in the local address book) performed by SQL Enterprise Manager or SQLMail (xp_sendmail). To do this, surround the email address with square brackets: [ ].
This can be done on an address-by-address basis using the Pager Email-Name box in the New Operator or Edit Operator dialog boxes. For more information, see Creating an Operator, earlier in this chapter.
This can be set globally for the server using the To and CC line templates in the Pager Email tab of the Alert Engine Option dialog box. For more information, see Setting Alert Engine Options, earlier in this chapter.
The square bracket notation is a feature of Windows NT Mail, not SQLMail or SQL Enterprise Manager.
When running SQL Enterprise Manager on the server computer, the mail integration features described in Creating an Operator, earlier in this chapter, are available only under these conditions:
You cannot specify the Auto Start Mail Client option using the Server Options dialog boxes of the setup or SQL Server programs.
HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
\SQLEW
Value: UseAddressBook
Type: REG_DWORD
Data: 0x1
The alert notification features of SQL Server 6.0 use SQLMail, which is written using the MAPI 0 API set. To use pager notifications, you need to install some form of mail-server-side software that can process inbound mail and convert this mail into a pager message. This software can take one of several approaches to achieve this, including these three:
For specific software requirements contact your pager service provider.
In general, it is a good idea to avoid excessing writing to the logs. If you have set up a substantial number of alerts, you may want to disable writing to the SQL Server error log altogether, and only write to the Windows NT application log. You should avoid using alerts too liberally (for example, do not send an alert every time a row in a table is changed).
To prevent writing to the SQL Server error log, use SQL Setup or SQL Enterprise Manager.