How It Works: SQL Server Engine Error Messages

  • Article

The Senior Escalation Engineers do various training and mentoring activities. As I do this I thought I would try to propagate some of this information on the blog.

The majority of SQL Server errors are handled by a central routine named ex_raise.   The routine takes a MAJOR and a MINOR identifier along with the severity, state and variable error message arguments.  The error number becomes (MAJOR * 100) + MINOR.  So for a deadlock error is 12, 5 or 12 * 100 + 5 = 1205.

The severity indicates the impact of the error on the current action.  The severities range from 0 to 21 and severity 16 and higher indicate a significant error.  For example if you attempt a backup to an invalid path the severity is 16 indicating the backup failed and you should consider this a business problem.   A duplicate primary key is often a severity 14.  This indicates the statement failed but this is something that is easily checked @@ERROR and corrected using an update instead of an insert.

The state in a reference to the location in the code where the error was encountered.  The states generally unique and allow support to determine the conditions that caused the error to be identified.  Whenever you work with SQL Server always get the Error, Sev and State to help fully understand the situation.

WARNING: The following should always be used with caution and under Microsoft Support supervision and may only be supported when used under such supervision.

Most errors can be used to trigger extended diagnostics.   The -y#### command line parameter can be used to trigger a mini-dump when the error is encountered.  For example -y1205 would capture a mini-dump each time a deadlock was encountered. Additional information will be placed in the error log as well as the LOG directory for the instance.

The dbcc dumptrigger('set' | 'clear', ###) command can be used to dynamically enabled and disable the -y behavior.   For example dbcc dumptrigger('set', 1205) is the same as -y1205 but does not require the SQL Server service to be restarted.

Bob Dorr
SQL Server Senior Escalation Engineer