SQL Server Error Messages

Posted By Sagar Patil

SQL Server comes with pre-defined error messages that can be raised when a particular condition occurs. Built-in error messages are stored in the sysmessages table of the master database. Depending on the severity level, error messages can be returned to the offending connection or could be recorded in the SQL Server error log. The severity level determines the type and seriousness of the problem that caused the error. The following table summarizes various error severity levels:

Error Severity Level Explanation
0 These are messages informing you of the completion status of a statement you executed, such as: “new role added”. Level 0 also includes informational warning messages such as: “Warning: Null value is eliminated by an aggregate or other SET operation.” Messages of level 0 are returned as information, not errors.
10 Informational message that is usually caused by incorrect information entered by the user. For example: “No rows were updated or deleted.”
11 through 16 These are errors that are caused by users and can be corrected by users. For example:

  • “User name ‘JohnDoe’ not found.”
  • “Cannot create an index on ‘authors21’, because this table does not exist in database ‘pubs’.”
  • “The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.”
  • “CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is ‘121’.”
17 This severity indicates insufficient resources for performing a requested operation. For example, SQL Server might have run out of locks, as in the following: “Sort failed. Out of space or locks in database ‘pubs’.” Be sure to adjust SQL Server configuration accordingly to avoid such errors in the future.
18 Non-fatal internal error has occurred. This means that SQL Server encountered errors in the internal code, but the connection is maintained. For example: “The server encountered a stack overflow during compile time.” or “Transaction manager has canceled the distributed transaction.” If your users continuously encounter such errors and you cannot resolve the problem you should ensure that you have applied the latest SQL Server service pack. If that does not help, contact Microsoft’s technical support.
19 This severity level indicates lack of resources, but not due to SQL Server configuration. Some of these are fairly harmless, for instance: “The log file for database ‘test’ is full. Back up the transaction log for the database to free up some log space.” This simply means that you need to backup the log or increase the size of the transaction log files. Other errors of severity 19 could be quite serious, for instance: “There is no room to hold the buffer resource lock %S_BUF in SDES %S_SDES. Contact Technical Support.” If you encounter such errors, contact Microsoft’s technical support ASAP. The Transact-SQL batch that caused error with severity 19 will terminate, but the connection will remain open. Error levels with severity 19 or higher are written to the SQL Server error log automatically.
20 Fatal error on the current connection. This means the session that encountered the error will log the error and then will be disconnected from SQL Server. For example: “Row compare failure.” or “Sort cannot be reconciled with transaction log.” Be sure to look up the resolution for such errors at support.microsoft.com – many of these are well documented. Severity level of 20 usually does not mean that database is damaged.
21 An error has occurred which affects all databases on the current instance of SQL Server. For example: “Error writing audit trace. SQL Server is shutting down.” or “Could not open tempdb. Cannot continue.” 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 Not encountered very often, this severity level is usually associated with hardware (disk or cache) errors. Level 22 indicates that table integrity is suspect. For example: “Could not locate row in sysobjects for system catalog ‘%.*ls’ in database ‘XYZ’. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.” You should run DBCC CHECKTABLE on a particular table or all tables in the database. The safest bet is to execute DBCC CHECKDB to examine the integrity of all tables. Executing DBCC CHECKDB with one of the REPAIR options can usually fix the problem. If the problem is related to index pages, drop and recreate the affected index. You might also have to restore the database from backup.
23 Severity of 23 indicates that the integrity of the entire database is affected and the database will be marked suspect. For example: “Possible schema corruption. Run DBCC CHECKCATALOG.” 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 Severity of 24 usually spells hardware failure; the error will look similar to the following: “I/O error %ls detected during %S_MSG at offset %#016I64x in file ‘%ls’.” You might have to reload the database from backup. Be sure to execute DBCC CHECKDB to check database consistency first. You might also wish to contact your hardware vendor.

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu