SQL Server error messages – severity levels – sys.messages

There are about 98318 error messages as of SQL Server 2008 - including repetitions for all the languages supported.

There are only 8938 errors types in sql server 2008 for English language and 11 languages are supported so 11 * 8938 = 98318


sys.messages catalog view represents one row per error message.

You can simply run select * from sys.messages to see all the error messages. You can see that every message has a unique message_id and the description in text column.

Another interesting column is severity. When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.

Following are the severity levels:

0-10

These are only Informational messages.

11-16

Errors that can be corrected by the user.

Example: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

11

A given object does not exist.

Example: Table '%.*ls' does not exist.

12

Indicates that queries are not requesting locks since they are using query hints.

Example: Could not continue scan with NOLOCK due to data movement.

13

Transaction deadlock errors.

Example: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

14

Security-related errors.

Example: The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

15

Syntax errors in T-SQL

Example: A TOP N value may not be negative.

16

Errors that can be corrected by the user.

17-19

Indicate software errors that cannot be corrected by the user.

17

Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.

Example: Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.

18

Indicates an issue in the Database Engine

Example: Replication-%s: agent %s failed. %s

19

Indicates that a non-configurable Database Engine limit has been exceeded. The current batch gets terminated. Error messages with a severity level from 19 through 25 are written to the error log.

Example: There is insufficient system memory in resource pool '%ls' to run this query.

20-25

These errors indicate system problems and are fatal errors. Error messages with a severity level from 19 through 25 are written to the error log.

Example: Unable to deallocate a kept page.

20

Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.

Example: Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator.

21

Unlike 20, this one indicates that a problem has been encountered that affects all tasks in the current database.

Example: An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.

22

Indicates that the table or index specified in the message has been damaged by a software or hardware problem.

Example: The row object is inconsistent. Please rerun the query.

23

Integrity of the entire database is in question because of a hardware or software problem.

Example: Possible schema corruption. Run DBCC CHECKCATALOG.

24

Indicates a media failure.

Example: The log record at LSN %S_LSN is corrupted.

Hope you like it.