:::: MENU ::::

Understanding Severity Levels of SQL Server Error Messages

  • Jan 23 / 2009
  • 0
dbDigger, Troubleshooting

Understanding Severity Levels of SQL Server Error Messages

In SQL Server a full error message for T-SQL consists of four parts normally. To outline the parts in messages consider following sample error message

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘count’.

  • Msg is message number, or it may be said error number. It is unique to every error description statement. If the error occurs in a stored procedure or trigger, it returns the line number in the routine.
  • Level is severity level of error message from 0 to 25. It points towards nature of error and its severity.
  • State is integer value between 1 and 127. It points to source of error message. The values for this parameter are specific to error message and are not documented by Microsoft.
  • Line points to line number in code that generated the error. The line may not be the exact line produced the error. In that case you may examine the control flow to detect exact source of error.

In second line there is error description statement that briefly describes the produced error.
If error occurred in set of defined T-SQL statements like stored procedures, functions etc. Then name of that source may also be included in full error message.
Among the above mentioned parts of a full error message, after the error statement itself, level is important information that helps to trouble shoot the error. Some error statements makes no idea without considering the associated error severity level. So how to get error severity levels summary to use these while trouble shooting T-SQL statements?
Severity level of errors range from 0 to 25. I will summarize the description of error levels at reasonable level of abstraction.

Level 00 – 09
These messages are not errors but just informational messages produced
by SQL Server Engine.

Level 10
These are also informational messages but report errors that are not severe and does not threat the execution. For sake of compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.

Level 11
Indicates that the given object or entity does not exist.

Level 12
A special severity for queries that do not use locking because of special hints provided in query. In some cases, read operations performed by these statements could result in inconsistent data due to fact that locking mechanism is not applied.

Level 13
Indicates errors due to transaction deadlock.

Level 14
Indicates permission and security-related errors, such as permission denied.

Level 15

Indicates presence of syntax errors in the Transact-SQL command.

Level 16
Indicates general errors that can be corrected by the user.

Level 17
Produced when of SQL Server becomes short of configurable resources. Example of such resources may be SQL Server locks, memory and disk space.

Level 18
roduced due to internal software problem in database engine. Statement is executed and connection to SQL Server engine is maintained. BOL recommends that system admin should be informed in case of an error with severity level 18.

Level 19
Produced when SQL Server becomes short of non configurable resources. Execution of current batch is terminated. These errors are written in error log.

Level 20
Produced by any problem with statement issued by the current process. Over all environment of SQL Server is not threatened except the related process. Errors of this level are also written in error log.

Level 21
Produced when an error occurs that affects all the processes of that specific database in SQL Server. Mostly it is unlikely that database itself is damaged. Errors of this level is also written in error log.

Level 22
Indicates the damage of an index or table. The problem may be in cache or on disk. If problem is on cache then just restart of SQL Server will work. If problem is on disk then use maintenance and trouble shooting commands to detect and fix the problems.

Level 23
Indicates that specific database is corrupt to some extent. Use commands to fix the problems. reason of corruption may be hard ware or software related.

Level 24
Indicates that a hardware problem occurs. You have to restore the database
and hard ware vendor may also be contacted in this case.

Level 25
Indicates that some type of system level error has occurred

Understanding and using the severity level concept along with error statement produced helps allot to efficiently detect and fix the errors. To have a complete default list of SQL Server error number, severity level and error description use following commands for SQL Server 2000, SQL Server 2005 and SQL Server 2008.

/*  
 Command to get default list of SQL Server error numbers and descriptions  
 */  
 --For SQL Server 2000  
 USE Master  
 SELECT * FROM SysMessages  
 GO  
 -- For SQL Server 2005, 2008  
 USE Master  
 SELECT * FROM SysMessages  
 WHERE MsgLangId = 1033  
 GO  

In case of SQL Server 2000 value for ‘MsgLangID’ is not required because it returns default language only. While SQL Server 2005 returns all language so you have to provide language ID as in this case we have provided for English. To get list of all languages and their IDs in SQL Server 2005 execute following T-SQL command.

Consult us to explore the Databases. Contact us