Troubleshooting Alerts

If you are experiencing problems with alerts, read the solutions detailed here.

An alert is not firing.

• Verify that the SQLServerAgent and EventLog services are running.
• Verify that the event appears in the Microsoft Windows NT application log.

  Launch the Windows NT Event Viewer. If the event is not in the log, check the log settings. On the Log menu, click Log Settings, and then in the Change Settings for Log box, select Application. If needed, set these options to the specified values.

Note Also check the Microsoft SQL Server error log; events written to the Windows NT application log are also written to the SQL Server error log. To focus the search on the cause of the problem, compare the dates and times for events between the SQL Server error log, the SQL Server Agent error log, and the Windows NT application log.

• Verify that the alert is enabled.
• Verify that the history values of the alert (for example, the occurrence count and last occurred values) are changing.
• Verify that the counter value is at, above, or below the defined threshold value for a minimum of 20 seconds.

  SQL Server Agent polls the performance counters at 20-second intervals.

  ---

  Important Using a frequency higher than 20 seconds increases the processing overhead for SQL Server.

  ---

  If a counter spikes for only a few seconds, which satisfies the performance condition, there is a high likelihood that SQL Server Agent will fail to see the spike; the alert will not fire.

An alert is firing, but the responsible operator is not receiving notification.

• Check the operator and notification information to verify you have entered the correct e-mail, pager, and net send addresses.
• Test the e-mail, pager, and net send addresses.
• Check the operator's on-duty schedule.
• Check the SQL Server Agent error log for any e-mail problems.

An alert is firing, but the notification is not timely.

The probable causes for this include:

• The Delay between responses setting for the alert is too high.
• The alert response is complex, requiring many operator notifications.

Note Send notifications to as few operators as possible. For example, send notifications to one group e-mail address rather than notifying several individual operators.

This error appears in the SQLServerAgent error log on Windows 95 or Windows 98 servers: "The common event system is being restarted after function ProduceEventsFromSS returned error 44, 'Unable To Connect'"

This may indicate incorrect registered server information. Verify that the registered server information for the local server is correct and that the registered login name is a member of the sysadmin fixed database role.

The Windows NT application log fills rapidly with the same error.

The CPU usage is high.

The number of alert responses is high.

Because SQL Server Agent both depends on and monitors SQL Server, SQL Server Agent can become caught in an endless loop of firing the same alert. This generally occurs when SQL Server runs out of an essential global resource and an alert has been defined on this event.

When the number of alerts raised exceeds the SQL Server Agent alert processing rate, a backlog is created.

To eliminate an alert processing backlog

1. Increase the amount of time in the Delay between responses setting.
2. Correct the global resource problem to prevent recurring alerts from using all your resources.
3. Configure an error to be nonalert-generating.

   ---

   Important Configuring an error to not generate an alert can be performed only within the registry. This solution should be used only as a last resort.

   ---

4. Clear the Windows NT application log if: the backlog is not clearing, you do not want to wait for SQL Server Agent to clear the backlog, or you want an empty, unpopulated Windows NT application log.

   ---

   Caution Clearing the Windows NT application log using the Clear All Events option on the Log menu deletes all events from the error log, including those unrelated to SQL Server.

   ---

To configure an error to not generate an alert

1. Start the Registry Editor.
2. Locate the following registry key:

   HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
\SQLServerAgent
\NonAlertableErrors

     

3. Type the error number.

   The list of nonalertable errors can be a maximum of 1,024 characters, should not contain spaces, and items must be separated by commas (,). Any error number in the list that appears after the number 0 will generate an alert. In this way, the entire list can be suspended temporarily if a 0 is the first list item. For example, assume that the list consists of

   1204,0,100

     

   In this example, only error number 1204 does not generate an alert. Because error number 100 follows error number 0 in the list, it will generate an alert.

Important Never remove the default nonalert-generating error, error 1204. Error 1204 defines those conditions known to lead to recursive alert generation. Removing this error will hamper attempts to resolve recursive alert generation.