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 Oct 14, 2002

SQL Server Agent - Introduction to Alerts - Page 2

By Alexzander Nepomnjashiy


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




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