I was recently asked how SQL Server handles exceptions in the core code of the engine. I looked around at various references and did not find a concise document.
When an exception is encountered in SQL Server error details such as the following are inserted into the SQL Server error log, a mini-dump is captured and saved in the LOG directory along with a .TXT file containing additional information. Then the connection is terminated which initiates a rollback for the active transaction.
2009-03-27 11:39:46.76 spid52 ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0001.txt
2009-03-27 11:39:46.85 spid52 Stack Signature for the dump is 0x00000000E8A23173
In C/C++ you may have seen exception logic for such exceptions as Access Violations (AV), Divide By Zero, Floating Point Overflow and others listed in the windows headers.
Executing common code path
catch(……..) -- could be the _except handler
Do something with the exception
SQL Server uses this logical approach, catching the exception and capturing the details in the error log, event log, mini-dump and .txt file.
The majority of SQL Server exceptions are considered fatal to the connection but not the process. SQL Server does install a default exception handler so if an exception is encountered that is NOT handled the handler can capture detailed information. An unhandled exception can result in process termination. SQL Server also installs handlers for issues such as runtime errors. Again, SQL Server attempts to capture information and terminate the process gracefully under these conditions.
Background processes such as lock monitor, lazy writer, checkpoint and others catch exceptions, handle them and continue. This is usually done by creating a new worker to perform the task and terminating the worker that encountered the exception.
Bob Dorr – Principle SQL Server Escalation Engineer