A Guide to SQL Severity and Error levels.

How to read SQL Server error messages


Here is a typical error message:

Server: Msg 547, Level 16, State 1, Procedure, Line #
Message Textà.


Message number û each error message has a number. You can find most of the message numbers in the table sysmessages in the master database. (There some special numbers like 0 and 50000 that do not appear there.) Message numbers from 50001 and up are user-defined. Lower numbers are system defined.

Message table

--For SQL Server 2000
SELECT *
FROM master..sysmessages
ORDER BY severity

--For SQL Server 2005
SELECT *
FROM master.sys.sysmessages
ORDER BY severity

Severity level û a number from 0 to 25. If the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in your SQL code have a severity level in the range 11-16. Severity 12 is not used. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and a severity of 20 or higher is fatal, the connection will be terminated.

State û a value between 0 and 127. The meaning of this item is specific to the error messages. Microsoft has not documented these values

Procedure û in which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).

Line û Line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.

Message text û the actual text of the message that tells you what went wrong. You can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.

Severity Information:
0 Messages with Level 0 are purely informational. A PRINT statement produces a message on severity level 0. These messages do not set @@error. Most query tools prints only the text part of a level 0 message.

1-9 These levels, too, are for informational messages/warnings. Query Analyzer and SQL Management Studio prints the message number, the level and the state, but not the procedure and line number for these messages.

10 This level does not really exist. It appears that SQL Server internally converts level 10 to level 0, both for its own messages when you use level 10 in RAISERROR.

11-16 These levels indicate a regular programming error of some sort. But it is not the case that level 16 is more serious than level 11.
11 û Specified Database Object Not Found
12 û Unused
13 û User Transaction Syntax Error
14 û Insufficient Permission
15 û Syntax Error in SQL Statements
16 û Miscellaneous User Error

Deadlock, for instance is level 13

17-25 Messages with any of these severity levels indicate some sort of resource problem (for instance running out of disk space), or internal error in SQL Server, or a problem with the operating system or hardware. The higher the severity, the more serious problems.
19-25 To use level 19 or higher in RAISERROR you must use the WITH LOG option, and you must have sysadmin rights.
20-25 Errors with these severity levels are so fatal, that they always terminate the connection.



These levels are documented in in the setion Troubleshooting->Error Messages->Error Message Formats->Error Message Severity Levels in Books Online.