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
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.
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.
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.
The use of alerts is necessary for constant control of database server operation and automatic response to non-routine situations, such as:
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.
Errors in the database server configuration (for example, an insufficient number
Insufficient database server system resources (for example, lack of free
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)
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
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
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
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