List of Errors and severity level in SQL Server with catalog view sysmessages
Few days back I have written “Error handling with “THROW” command in SQL Server 2012”. After reading this article, one of the regular blog readers has asked me question why I have used 16 severities for the error?
This is really interesting question so I thought to answer him in the form of this blog post so that everyone who is unaware with error messages in SQL Server can be benefited.
16 is a default severity level and used for most user exception and that was the reason I have used it. You can get a list of error messages along with its severity level and error number from following catalog view.
SELECT * FROM master.dbo.sysmessages WHERE error=8134 -- error message number I got in previous article AND msglangid = 1033; --language selection, 1033 represents US english
There are total number of 10542 error message comes by default for language id 1033 (US English). Error messages given in total 22 language so 10542 error * repeated 22 times = 231924 rows in sysmessages.
Generally we have sixteen different severity level in SQL Server 2012 and each severity represents criticalness of an error. You can get a list of severity from the following TSQL.
SELECT DISTINCT severity FROM master.dbo.sysmessages;
Here is the brief description of different severity.
Severity level 0-10: These are just information message not actual error.
Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 in previous article and hence we got severity error 16.
Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.
Severity Level 18: This error represents nonfatal internal software error.
Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.
Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.
Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.
Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.
Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.
Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.
Older version of SQL Server had Severity Level 25 as well but it is unexpected system error and doesn’t list in SQL Server 2012’s sysmessages catalog view.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.