Microsoft SQL Server 2000 Error Messages
If you run a query and accidentally make a mistake by entering a table that does not exist in the database, what happens? SQL Server returns an error message. Actually, SQL Server reacts to all errors in the same manner, whether those errors are generated by users, databases, objects, or the system. SQL Server returns a formatted error message and/or writes the error message to the error log and/or event log. Here is a quick example that executes a SQL statement to update a nonexistence table in the pubs database. The SQL statement for the example is as follows:
UPDATE new_authors Set author1 = "Spenik", author2 = "Sledge", title="Microsoft SQL Server DBA Survival Guide"
When the statement is executed, the following error message is returned:
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'new_authors'.
The first thing presented in the error message is the message number, severity level, state, and line number. To most users, these numbers are just garbage to be ignored, so they skip down to the message and try to resolve the problem. In reality, the error message number is very useful for obtaining more error information. You can use the severity levels to help find errors that need to be handled. When tracking a problem, always write down all the error information, including the message number, severity level, and state. In many cases, these will be of more assistance than the actual message.
Examine the format of a standard SQL Server error message.
Error Message Number
Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error.
You can define your own error messages. User-defined error message numbers must be greater than 50,000 and less than 2,147,483,647
The error severity levels provide a quick reference for you about the nature of the error. The severity levels range from 0 to 25.
|0 to 10||Messages with a severity level of 0 to 10 are informational messages and not actual errors.|
|11 to 16||Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.|
|17||Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.|
|18||Severity level 18 messages indicate nonfatal internal software problems.|
|19||Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.|
Severity errors 19 through 25 are fatal errors and can only be used via RAISERROR by members of the fixed database role sysadmin with the with log option required. Severity 0 - 18 can be used by all users. When a fatal error occurs (20 - 25), the running process that generated the error is terminated (nonfatal errors continue processing). For error severity levels 20 and greater, the client connection to SQL Server is terminated.
|20||Severity level 20 indicates a problem with a statement issued by the current process.|
|21||Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.|
|22||Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.|
|23||Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.|
|24||Severity level 24 indicates a hardware problem.|
|25||Severity level 25 indicates some type of system error.|
The error state number is an integer value between 1 and 127; it represents information about the source that issued the error (such as the error can be called from more then one place).
The error message is a description of the error that occurred. The error messages are stored in the sysmessages system table. Figure 1 shows a query result of the sysmessages table.
Figure 1 - Query results of sysmessage using the Query Analyzer.
To use the SQL Enterprise manager to view error messages or search for error messages, select a server and right-click. Select All Tasks and Manage SQL Server Messages, and the SQL Server Message dialog box appears. Using the dialog box, you can search for error messages by error number or key words.
This article is based on information from Microsoft SQL Server 2000 DBA Survival Guide.