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.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles