How It Works: SQL Server Engine Exception Handling

[mssql-support]

First published on MSDN on May 26, 2009

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.76 spid52 * *******************************************************************************

2009-03-27 11:39:46.76 spid52 *

2009-03-27 11:39:46.76 spid52 * BEGIN STACK DUMP:

2009-03-27 11:39:46.76 spid52 * 03/27/09 11:39:46 spid 1

2009-03-27 11:39:46.76 spid52 *

2009-03-27 11:39:46.76 spid52 * StackDump ()

2009-03-27 11:39:46.76 spid52 *

2009-03-27 11:39:46.78 spid52 *

2009-03-27 11:39:46.78 spid52 *

2009-03-27 11:39:46.78 spid52 * MODULE BASE END SIZE

2009-03-27 11:39:46.78 spid52 * sqlservr 0000000001000000 0000000003651FFF 02652000

2009-03-27 11:39:46.78 spid52 * ntdll 0000000077EC0000 0000000077FF8FFF 00139000

2009-03-27 11:39:46.78 spid52 * kernel32 0000000077D40000 0000000077EB2FFF 00173000

2009-03-27 11:39:46.78 spid52 * MSVCR80 0000000078130000 00000000781F8FFF 000c9000

2009-03-27 11:39:46.78 spid52 * msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000

2009-03-27 11:39:46.78 spid52 * MSVCP80 000000007C420000 000000007C528FFF 00109000

2009-03-27 11:39:46.78 spid52 * ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000

….. << Lines Removed >> …

2009-03-27 11:39:46.78 spid52 * sqlevn70 000000000F4F0000 000000000F698FFF 001a9000

2009-03-27 11:39:46.78 spid52 * dbghelp 00000000093B0000 000000000950CFFF 0015d000

2009-03-27 11:39:46.78 spid52 *

2009-03-27 11:39:46.78 spid52 * P1Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * P2Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * P3Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * P4Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * P5Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * P6Home: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * ContextFlags: 000000000010000B:

2009-03-27 11:39:46.78 spid52 * MxCsr: 0000000000001F80:

2009-03-27 11:39:46.78 spid52 * SegCs: 0000000000000033:

2009-03-27 11:39:46.78 spid52 * SegDs: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * SegEs: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * SegFs: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * SegGs: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * SegSs: 000000000000002B:

2009-03-27 11:39:46.78 spid52 * EFlags: 0000000000000246:

2009-03-27 11:39:46.78 spid52 * Rax: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * Rcx: FFFFFFFFFFFFFFFF:

2009-03-27 11:39:46.78 spid52 * Rdx: 0000000000000003:

2009-03-27 11:39:46.78 spid52 * Rbx: 00000000048BF068: FFFFFFFFFCFEF500 0000000000000048 0000000000000001 0000000000000000 0000000000000000 0000000000000000

2009-03-27 11:39:46.78 spid52 * Rsp: 00000000048BF018: 0000000077D6CFFB 0000000000000019 00000000015956C1 0000000000000000 00000000801051E0 00000000048BF068

2009-03-27 11:39:46.78 spid52 * Rbp: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * Rsi: 00000000048BF0C0: 0000000000000084 0000000000000070 0000000000000078 0000000000000003 0000000000080000 000007FFFFEE0000

2009-03-27 11:39:46.78 spid52 * Rdi: 0000000000000003:

2009-03-27 11:39:46.78 spid52 * R8: 00000000048BEFC0: 0000000210155000 000000020EF8C000 FFFFFFFF00007CD2 0000000006FE3000 0000000006FD3000 0000000000049AE0

2009-03-27 11:39:46.78 spid52 * R9: 0000000000000060:

2009-03-27 11:39:46.78 spid52 * R10: FFFFFFFFFFFFFFFF:

2009-03-27 11:39:46.78 spid52 * R11: 0000000000000246:

2009-03-27 11:39:46.78 spid52 * R12: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * R13: 0000000000000000:

2009-03-27 11:39:46.78 spid52 * R14: 0000000000000003:

2009-03-27 11:39:46.78 spid52 * R15: 00000000000013B0:

2009-03-27 11:39:46.78 spid52 * Rip: 0000000077EF0F9A: 8B4C9066906666C3 050F00000059B8D1 8B4C9066906666C3 050F0000005AB8D1 8B4C9066906666C3 050F0000005BB8D1

2009-03-27 11:39:46.78 spid52 * *******************************************************************************

2009-03-27 11:39:46.78 spid52 * -------------------------------------------------------------------------------

2009-03-27 11:39:46.78 spid52 * Short Stack Dump

2009-03-27 11:39:46.82 spid52 0000000077EF0F9A Module(ntdll+0000000000030F9A)

2009-03-27 11:39:46.82 spid52 0000000077D6CFFB Module(kernel32+000000000002CFFB)

2009-03-27 11:39:46.82 spid52 0000000077D6BB01 Module(kernel32+000000000002BB01)

2009-03-27 11:39:46.82 spid52 0000000001595AB4 Module(sqlservr+0000000000595AB4)

2009-03-27 11:39:46.82 spid52 0000000002B43967 Module(sqlservr+0000000001B43967)

2009-03-27 11:39:46.82 spid52 0000000002B489E1 Module(sqlservr+0000000001B489E1)

2009-03-27 11:39:46.82 spid52 00000000016BC32E Module(sqlservr+00000000006BC32E)

2009-03-27 11:39:46.82 spid52 00000000016BC5C9 Module(sqlservr+00000000006BC5C9)

2009-03-27 11:39:46.82 spid52 00000000016B5CC4 Module(sqlservr+00000000006B5CC4)

2009-03-27 11:39:46.82 spid52 000000000155E837 Module(sqlservr+000000000055E837)

2009-03-27 11:39:46.82 spid52 0000000001522F59 Module(sqlservr+0000000000522F59)

2009-03-27 11:39:46.82 spid52 00000000015372B0 Module(sqlservr+00000000005372B0)

2009-03-27 11:39:46.83 spid52 00000000014F72F8 Module(sqlservr+00000000004F72F8)

2009-03-27 11:39:46.83 spid52 00000000781337D7 Module(MSVCR80+00000000000037D7)

2009-03-27 11:39:46.83 spid52 0000000078133894 Module(MSVCR80+0000000000003894)

2009-03-27 11:39:46.83 spid52 0000000077D6B6DA Module(kernel32+000000000002B6DA)

2009-03-27 11:39:46.85 spid52 * -------------------------------------------------------------------------------

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.

try
{

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