Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 24, 2003

Troubleshooting SQL Server Alerts

By Alexander Chigrik

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM