Alert System Troubleshooting

Under unusual circumstances, the SQL Executive alert engine can inadvertently become caught in the endless recursive firing of the same alert. Such circumstances generally arise when the SQL Server runs out of some essential global resource, such as locks, and an alert has been defined on this event, either explicitly by error number or implicitly by severity level.

In such circumstances, the following sequence starts and can be stopped only when the SQL Server's global resource problem is manually fixed:

1. SQL Server reports a global resource error.
2. SQL Executive invokes the alert for this event.
3. The alert, as part of its response, attempts to perform some action on the SQL Server.
4. This action causes the server to hit the global resource error again.
5. The steps repeat.

These are symptoms of a repeating alert:

• The event log rapidly fills up with the same error.
• An unusually high CPU usage occurs.
• Numerous alert responses are received for the alert involved.

Should you encounter a problem, it is possible to configure the SQL Executive alert engine to treat specific error numbers as non-alert-generating errors. That is, if the error occurs and an alert has been defined on it, explicitly by error number or implicitly by severity level, then SQL Executive will not fire the alert. Instead it will write a message (event 323) to the Windows NT event log stating that the alert was ignored according to the non-alert-generating status that was assigned to it.

    To configure an error to be non-alert-generating

1. At the command prompt, invoke the Registry editor by typing:

   regedt32
     

2. Access the directory tree, as follows:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLExecutive
     

3. In the NonAlertableErrors list, enter the error number. The list can be up to 255 characters long, should not contain spaces, and each item must be separated with a comma (,) from the previous item. Any error number that appears after the number 0 in the list will be ignored and will generate an alert. Thus, the whole list can be temporarily suspended by adding 0 as the first item in the list.

SQL Enterprise Manager and the stored procedures sp_addalert and sp_updatealert use the NonAlertableErrors list to restrict which errors can be monitored by an alert.

Important There should seldom be the need to modify the default list of entries.

Examples

A.    Default Alert Entries

This example shows the default list of non-alert-generating errors. These errors should never be removed.

1204,4002    
  

B.    Add Error 100 to Entries

This example adds error 100 to the list of non-alert-generating errors:

1204,4002,100
  

C.    Interrupt Error List

This example shows that having 0 within the list suspends the processing of the rest of the list. Only errors 1204 and 4002 are non-alert-generating; error 100 will generate an alert.

1204,4002,0,100