When working with SQL Server, it is common to encounter errors. Each error message displayed by SQL Server has an associated error message number that uniquely identifies the type of error. Understanding these error messages can help you troubleshoot and resolve issues more effectively.

The error severity levels provide a quick reference for you about the nature of the error. The severity level ranges from 0 to 25, with 0 to 10 being informational messages and not actual errors. Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, an invalid update query may result in an error message with a severity level of 16.

Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. This type of error can be corrected by the database administrator (DBA) or the database owner. Severity level 18 messages indicate nonfatal internal software problems, while severity level 19 indicates that a nonconfigurable resource limit has been exceeded.

Severity level 20 indicates a problem with a statement issued by the current process, while severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database. Severity level 22 means a table or index has been damaged, and restarting SQL Server can help determine the extent of the problem. If the problem is in the cache and not on the disk, the restart corrects the issue. Otherwise, using the DBCC command can help determine the extent of the damage and the required action to take.

Severity level 23 indicates a suspect database, and using the DBCC commands can help determine the extent of the damage and the proper action to take. Severity level 24 indicates a hardware problem, while severity level 25 indicates some type of system error.

It is important to pay attention to these error messages and their severity levels when troubleshooting SQL Server issues. By understanding the nature of the error, you can take appropriate actions to resolve the problem.

To view all the system messages in SQL Server, you can run the following statement in the query analyzer:

SELECT * FROM master.dbo.sysmessages

By referring to the sysmessages system table, you can access the error messages and their associated information.

Remember, error messages are valuable clues that can help you identify and resolve issues in your SQL Server environment. Take the time to understand the error messages and their severity levels, and use them as a guide in troubleshooting and resolving problems.

For more information on SQL Server error severity levels, you can refer to the MSDN Error Severity Level documentation.