As SQL Server Database Administrator or Developer you might have noticed different types of error messages logged in SQL Server Error Log. Usually most of the SQL errors are in the below format:
Error: <number> : Severity: <number>; State: <number>
Usually most of the times you would find an error description along with the error number. Some times you might want to find out what the particular error number means . You can query sys.messages to find description for the error message as below.
SELECT * FROM sys.messages WHERE message_id = <error_number> and language_id = 1033
(1033 stands for English here and there are different codes for different languages)
State in the error message format is useful for developers where it points to the exact piece of code where the error is raised for bug tracking.
Severity of the error determines the type of problem indicated by SQL Server Database Engine. Below are different severity levels and their description.
0-10: Information messages only.
11-16: Errors caused by users that can be corrected by users, for example, “table does not exist in database.”
17: Insufficient resources. Insufficient resources to carry out the statement, for example: no more disk space for the database.
18: Non-fatal internal error. An internal error, that does not cause the connection to terminate, for example, stack overflow during compilation. Level 18 errors may occur, for example, when the SQL Server query processor detects an internal error during query optimization.
19: SQL Server resource problem. A non-configurable internal limit has been exceeded (for example, log file is full) and the current batch is terminated. Severity level 19 errors occur rarely, but must be corrected. Note: Errors with a severity level of 19 or higher terminate the current batch.
Severity levels 20-25 indicate system problems; these are fatal errors, indicating that the process is no longer running. The process records information about what occurred before terminating, and the client connection to SQL Server closes. Error messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged.
20 – Fatal error on current connection: The current process has encountered a problem; this does not usually mean that database is damaged. Refer to Microsoft support sites for more information about your specific error.
21 – Fatal error on database: An error has occurred which affects all processes in current database. A severity level of 21 usually does not mean any database is damaged. You might have to review the contents of system tables and the configuration options to resolve errors of this severity.
22 – Table integrity fatal error: Not encountered very often; indicates that table integrity is suspect. Usually related to hardware, but the problem may exist in cache only, rather than on disk.
Run DBCC CHECKDB to examine the integrity of all tables. Running DBCC CHECKDB with the REPAIR option may fix the problem. If restarting does not help, the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem.
For example, if the message tells you that SQL Server has found a row with a length of 0 in a non-clustered index, delete the index and rebuild it.
23 – Database integrity fatal error: Problem with the integrity of the entire database; the database will be marked suspect. Run DBCC CHECKDB.
These types of errors are usually caused by hardware issues. More than likely you will have to restore the database from backup. Run DBCC CHECKDB after restoring to ensure that the database is not corrupt.
24 – Media failure: Indicates some kind of hardware failure; you might have to reload the database from backup. Run DBCC CHECKDB to check database consistency first. You might also wish to contact your hardware vendor.