Troubleshooting SQL Server Alerts
September 24, 2003
If you have problems with Microsoft SQL Server alerts, review this troubleshooting checklist to find potential solutions.
1. Check that you are using the latest SQL Server service pack.
Because many alert bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. Check "How can I check what SQL service pack am I running?" to find out which SQL Server service pack you are running.
2. Check that the account the SQLServerAgent services runs under is a member of the Domain Users group.
The LocalSystem account does not have network access rights. Therefore, if you want to forward events to the application logs of other Windows NT or Windows 2000 computers, or your jobs require resources across the network, or you want to notify operators through e-mail or pagers, you must set the account the SQLServerAgent service runs under to be a member of the Domain Users group.
3. If all of the alerts are not firing, check that the SQLServerAgent and EventLog services are running.
These services must be started, if you need the alerts to be fired. So, if these services are not running, you should run them.
4. If an alert is not firing, make sure that it is enabled.
The alert can be enabled or disabled. To check if an alert is enabled, you can do the following:
5. Check the history values of the alert to determine the last date that the alert worked.
To view the history values of the alert, you can do the following:
6. Verify that the counter value is maintained for at least 20 seconds.
Because SQL Server Agent polls the performance counters at 20 second intervals, if the counter value is maintained for only a few seconds (less than 20 seconds), there is a high likelihood that the alert will not fire.
7. Check the SQL Server error log, SQL Server Agent error log, and Windows NT or Windows 2000 application log to get a more detailed error description.
Comparing the dates and times for alert failure events between the SQL Server error log, the SQL Server Agent error log, and the Windows NT or Windows 2000 application log can help you to determine the reason of the failure.
8. If the alert fires, but the responsible operator does not receive notification, try to send 'e-mail', 'pager', or 'net send' message to this operator manually.
In most cases, this problem arises when you have entered an incorrect 'e-mail', 'pager', or 'net send' addresses. If you can send an 'e-mail', 'pager', or 'net send' message manually to this operator, check the account the SQL Server Agent runs under, as well as the operator's on-duty schedule.
9. If the alert fires, but the notification is not timely, decrease the 'Delay between responses' setting for the alert and try to send notifications to as few operators as possible.
To decrease the 'Delay between responses' setting for the alert, you can do the following:
10. Alert cannot send e-mail notification with xp_logevent or RAISERROR.
This is an SQL Server 7.0 and SQL Server 2000 bug. This happens if the alert is defined to be restricted to a specific database other than the master database. To work around this, you can define the alert on the master database, or all databases. To define the alert on all databases, you can do the following:
11. Alerts are triggered incorrectly when Security Auditing is enabled.
This is an SQL Server 7.0 and SQL Server 2000 bug. This bug was fixed in SQL Server 2000 service pack 1 and in SQL Server 7.0 service pack 4. To work around this problem, you can disable Security Auditing, or you can install the service packs.
12. After Installing SQL Server 7.0 service pack 3, SQL Server Agent alerts may fail to work.
This is an SQL Server 7.0 service pack 3 bug. To work around this, you should install SQL Server 7.0 service pack 4.
13. Responses for performance condition alerts are sent every 20 seconds, regardless of the 'Delay between responses' setting for the alert.
This is an SQL Server 7.0 bug. To work around this, you should install SQL Server 7.0 service pack 1 or higher.