Trouble shooting Alerts

Troubleshooting SQL Server Alerts

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:

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 the appropriate alert to see if the alert is
enabled.

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:

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 the 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 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:

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 the appropriate alert and choose the 'Response'
tab.
5. Specify a new 'Delay between responses' setting.

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:

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 the appropriate alert and choose '(All Databases)'
in the Database name combobox.

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.