
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Troubleshooting SQL Server Alerts

Alexander Chigrik
chigrik@mssqlcity.com

If you have problems with Microsoft SQL Server alerts, review this troubleshooting checklist to find potential solutions.

*****
1. Check that you use 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.
To check what SQL Server service pack are you running, see this link:
How can I check what SQL service pack am I running?

*****
2. Check that account the SQLServerAgent services runs under is a member of the Domain Users group.

The LocalSystem account does not have network access rights, so 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. Check that the SQLServerAgent and EventLog services are running, if all the alerts are not firing.

These services must be started, if you need the alerts be fired. So, it these services are not running, you should run them.

*****
4. Check that the alert is enabled, if an alert is not firing.

The alert can be enabled or disabled. To check that alert is enabled, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click appropriate alert to see that alert enables.

*****
5. Check the history values of the alert to determine the last date the alert worked fine.

To view the history values of the alert, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click appropriate alert to see the alert history.

*****
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 SQL Server error log, SQL Server Agent error log, and Windows NT or Windows 2000 application log to get 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 not receive notification, try to send 'e-mail', 'pager', or 'net send' message to this operator manually.

In most cases, this problem was arisen because you have entered incorrect 'e-mail', 'pager', or 'net send' addresses. If you can send 'e-mail', 'pager', or 'net send' message manually to this operator, check the account the SQL Server Agent runs under and check 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:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click appropriate alert and choose the 'Response' tab.
5. Specify the new 'Delay between responses' setting.

*****
10. Alert cannot send e-mail notification with xp_logevent or RAISERROR.

This is SQL Server 7.0 and SQL Server 2000 bug. You can get this problem, if the alert is defined to be restricted to a specific database other than the master database. To work around this, you can define alert on the master database, or all databases. To define alert on all databases, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click appropriate alert and choose '(All Databases)' in the Database name combobox.

*****
11. Alerts triggered incorrectly when Security Auditing is enabled.

This is 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 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 SQL Server 7.0 bug. To work around this, you should install SQL Server 7.0 service pack 1 or higher.

*****

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc