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.
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.
Indicates that the given object or entity does not exist.
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.
Indicates errors due to transaction deadlock.
Indicates permission and security-related errors, such as permission denied.
Indicates presence of syntax errors in the Transact-SQL command.
Indicates general errors that can be corrected by the user.
Produced when of SQL Server becomes short of configurable resources. Example of such resources may be SQL Server locks, memory and disk space.
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.
Produced when SQL Server becomes short of non configurable resources. Execution of current batch is terminated. These errors are written in error log.
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.
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.
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.
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.
Indicates that a hardware problem occurs. You have to restore the database
and hard ware vendor may also be contacted in this case.
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
SELECT * FROM SysMessages
-- For SQL Server 2005, 2008
SELECT * FROM SysMessages
WHERE MsgLangId = 1033
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.