SQL Server Agent - Introduction to Alerts

Monday Oct 14th 2002 by Alexzander Nepomnjashiy
Share:

Alexzander Nepomnjashiy begins a new series that will focus on SQL Server alerts and errors. This initial article serves as an introduction to alerts and the various errors you might encounter as a database administrator.


This article marks the beginning of a new series that will focus on alerts and errors within the SQL Server Agent subsystem. A pre-defined set of demo Alert templates is included in every Microsoft SQL Server 7.0 installation, and the opportunity exists to create a variety of custom alerts. This initial article in the series will serve as an introduction to alerts and the various errors you might encounter as a database administrator.

So, what is an alert? According to SQL Server Books Online: "...an alert is a definition that matches one or more SQL Server events and a response, should those events occur". In general, an administrator cannot control the occurrence of events but can control the response to those events with alerts. Alerts can be defined to respond to SQL Server events by:

  •  Notifying one or more operators by sending them an e-mail (or a page) or by notifying them "through the network" (by a net send command);
  •  Forwarding the event to another server;
  •  Executing a correction job to address the problem that has occurred.
These actions are to be taken when specific events occur, such as a specific error, errors of certain severities or when a database reaches a defined limit of free space available.


How SQL Alerts Work

All events concerned with Microsoft SQL Server operation are written to the Microsoft Windows NT Application Log. From time to time, the SQL Server Agent views this Application Log, watching for originating errors. At the same time, the SQL Server Agent accesses the system table sysalerts located in the MSDB database, which contains information about all alerts defined at the database server level. If the agent discovers an event that meets the required trigger of an alert, this alert is then fired.

ATTENTION: The SQL Server pre-defined set of alerts serves only as a template. By default, SQL Server only counts the number of errors that have occurred; without additional configuration, the server will never notify operators or execute a job.

ATTENTION: Alerts will not run automatically until the SQL Server Agent service has been started.


Using Alerts

The use of alerts is necessary for constant control of database server operation and automatic response to non-routine situations, such as:
  • Errors in the database server configuration (for example, an insufficient number of locks);
  • Insufficient database server system resources (for example, lack of free disk space).
  • Problems with users' connections to a database server;
  • Violation of the structure or integrity of database tables and indexes (common problems with the server's hardware)
The combined use of jobs and alerts is probably the best and most effective method for handling strife with contingencies. For example, those concerned with a shortage of file space for the transaction log can prevent an emergency by starting a previously created job that will fulfill a truncation of the transaction log. The combination of jobs and alerts also makes for a convenient method of managing mobile users, as they often do not have sufficient preparation for the execution of the administrative tasks on their PC.


Page 2: Pre-defined Alerts and Their Triggering Errors



Pre-Defined Alerts and Their Triggering Errors

Every Microsoft SQL Server 7.0 installation has a pre-defined set of demo Alert templates (see below). Again, by default, SQL Server only counts the number of errors that have occurred and, without additional configuration, will never notify operators or execute a job.
  • Error with Severity 17 (Insufficient Resources) - These messages indicate that the statement caused Microsoft SQL Server 7.0 to run out of resources (such as the number of locks or free disk space available for databases) or to exceed a limit set by the DBA;

  • Error with Severity 18 (Non Fatal Internal Error) - These messages indicate that there is some type of internal software problem; however, the statement is able to finish and the connection to SQL Server is maintained. Although errors with severity equal to 18 are informational rather than critical, the DBA should be informed about them.

  • Error with Severity 19 (Fatal Error in Resources) - These messages indicate that some non-configurable internal limit related to system resources has been exceeded and the current batch process is terminated;

  • Error with Severity 20 (Fatal Error in the Current Process) - These messages indicate that a statement has encountered a problem. These errors affect only current user processes, and this client connection is terminated. Error messages with a severity level of 20 are considered CRITICAL, FATAL ERRORS;

  • Error with Severity 21 (Fatal Error in Database Process) - These messages indicate that SQL Server encountered a problem that affects all processes in the current database, and all user processes are terminated. Error messages with a severity level of 21 are also considered CRITICAL, FATAL ERRORS;

  • Error with Severity 22 (Fatal Error: Table Integrity Suspect) - These messages indicate the integrity of a table or index (the name of which is specified in the error message) is suspect due to a software or hardware problem. Run DBCC CHECKDB to determine if other objects in the database are also damaged. It is possible that the problem is in the cache and not on the disk itself, which means that restarting the database server can correct the problem. If the problem involves the disk, restarting will not help; in this case, use DBCC to repair the problem. In some cases, it may be necessary to restore the database. To continue working, you must reconnect to SQL Server. Error messages with a severity level of 22 are considered CRITICAL, FATAL ERRORS;

  • Error with Severity 23 (Database Integrity Suspect) - These messages indicate that the integrity of the entire database is suspect due to a hardware or software problem. Run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache (in which case restarting the database server can correct the problem) and not on the disk itself (in this case, restarting will not help, so DBCC will be needed to repair the problem). It may be necessary to restore the database. To continue working, you must reconnect to SQL Server. Error messages with a severity level of 23 are considered CRITICAL, FATAL ERRORS;

  • Error with Severity 24 (Fatal Error: Hardware Error) - These messages indicate some type of media failure (probably with your database server disk subsystem). Call your hardware vendor (optionally), replace any and all failed hardware components and reload your databases from the latest backup set. Error messages with a severity level of 24 are also considered CRITICAL, FATAL ERRORS;

  • Error with Severity 25 (Fatal Error) - These messages indicate general fatal errors. All user processes and connections to the database server are terminated. Call your primary software and hardware vendors to diagnose and correct the issue. Error messages with a severity level of 25 are considered to be CRITICAL, FATAL ERRORS;

  • The log file is full - These messages indicate that SQL Server cannot allocate sufficient additional free space needed for expanding the database.

  • The tempdb database is full - These messages indicate that SQL Server cannot allocate sufficient additional free space needed for expanding the tempdb system database.


Tips to Remember

In conclusion, here are some tips to remember:
  • Error messages with a severity equal to 10 are primarily informational.

  • Error messages with a severity level from 11 to 16 are generated by users and probably can be corrected by them.

  • Messages with a severity level from 17 to 19 are generated by SQL Server systems and/or involve resource errors.

  • Error messages with a severity from 20 to 24 are considered to be fatal system problems and indicate: fatal errors in current/«database-wide» processes (severity 20/21); table/database integrity errors (severity 22/23); hardware errors (severity 24); general fatal errors (severity 25).

  • Error messages with a severity level from 19 to 25 are subject of logging to the SQL Server Error Log.

  • In the case of an error with a severity from 19 to 25, there is a high probability that the only way to restore SQL Server will be restoring using the latest and consistent (error free) backup sets.

  • You can specify your own custom messages (with RAISERROR syntax) with error numbers starting from 50001, and severity levels from 0 through 18 (SQL Server database administrators can use severity level from 19 through 25 as well).


See All Articles by Columnist Alexzander Nepomnjashiy


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved