In SQL Server, there is a system table that stores all possible error states and their severity levels. Error severity ranges from 0 to 25 and defines the seriousness of an error. All sql server error messages that you may encounter are stored in the sys.messages table.
USE MASTERSELECT * FROM SYS.MESSAGESWHERE language_id = 1033
The table contains approximately 300,000 informational and error messages in all language variations of SQL Server. Therefore, when querying the table, we need to restrict the language to 1033 (English) or another language according to preferences.
Classification of Error Severity
- 0 – 10 -> Informational messages, not considered errors – example: “Data truncation error. Length … exceeds maximum length .. for type …”
- 11 – 16 -> Errors caused by the user’s side, such as running a poorly written script – example: “Invalid column name …”
- 17 -> Indicates server resource problems, such as locks (deadlock, etc.) – example: “There is insufficient memory available in the buffer pool.”
- 18 -> Indicates a problem with server software – example: “The transaction has been stopped because it conflicted with the execution of a FILESTREAM close operation using the same transaction. The transaction will be rolled back.”
- 19 -> Exceeding resource limits (non-configurable in SQL Server) – example: “There is insufficient system memory in resource pool … to run this query.”
- 20 -> Error at the process level – example: “Error while reading resource manager notification from Kernel Transaction Manager (KTM)”
- 21 -> SQL server has identified an issue affecting all processes in the database – example: “The database ID %d already exists.”
- 22 -> Serious table or index corruption – example: “The schema of a table created by InternalBaseTable is corrupt.”
- 23 -> Indicates database corruption – example: “Corruption in database … possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.”
- 24 -> Hardware problem – example: “The MSSQLServer service terminated unexpectedly. Check the SQL Server error log and operating system error logs for possible causes.”
- 25 -> Some system errors – there is no such message in SQL Server 2017 (applies to previous editions)
I recommend going through the article on how to work with error messages using TRY/CATCH.
Source: Microsoft Documentation
