Service Broker/Snapshot conflict
I have recently run into a problem with the service broker and replication. Whenever we apply a snapshot to a table, SQL server crashes.
Here's a break down of what happens:
1. Replication/Snapshot application goes fine
2. Replication/Snapshot application causes SQL server to crash and does so continuously once it has done it once.
3. We delete our only created Service Broker service and the Replication/Snapshot application does NOT crash SQL server and continues to operate normally.
At first this did not seem right, but we have been able to repeat it enough times to show at least a correllation between the two, if not making the Service Broker the direct cause.
The service broker is attached to 1 queue and is looking for event notifications on ALTER/CREATE for proc, view, table, function. The queue itself does nothing but hold these messages (an external service reads them).
As far as the snapshot goes, the server crashing was applying a snapshot when it crashed.
Has anyone run into this problem before? And have you found a fix?
Any help is greatly appreciated. I have attached one of many dump files (I can put up more if needed) and can provide additional details if necessary.
Dump File: Memory
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server
version 9.00.2153.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Current time is 16:00:26 08/14/06.
8 Unknown CPU 9., 2993 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
MemoryLoad = 9%
Total Physical = 32762 MB
Available Physical = 29517 MB
Total Page File = 35529 MB
Available Page File = 33881 MB
Total Virtual = 8388607 MB
Available Virtual = 8355376 MB
***Stack Dump being sent to Y:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0029.txt
SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/14/06 16:00:26 spid 54
*
*
* Exception Address = 0000000089193440 Module(UNKNOWN+0000000000000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 0000000089193440
* Input Buffer 84 bytes -
* 6 if @@trancount > 0 commit tran
*
*
* MODULE BASE END SIZE
* sqlservr 0000000001000000 00000000035F5FFF 025f6000
* ntdll 0000000078EC0000 0000000078FF8FFF 00139000
* kernel32 0000000078D40000 0000000078EB1FFF 00172000
* MSVCR80 0000000078130000 00000000781FEFFF 000cf000
* msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000
* MSVCP80 000000007C420000 000000007C530FFF 00111000
* ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000
* RPCRT4 000007FF7FD30000 000007FF7FED8FFF 001a9000
* USER32 0000000078C30000 0000000078D3BFFF 0010c000
* GDI32 000007FF7FC90000 000007FF7FD28FFF 00099000
* CRYPT32 000007FF7D180000 000007FF7D2DEFFF 0015f000
* MSASN1 000007FF7D150000 000007FF7D179FFF 0002a000
* Secur32 000007FF7E7F0000 000007FF7E811FFF 00022000
* MSWSOCK 000007FF76FE0000 000007FF7705BFFF 0007c000
* WS2_32 000007FF77150000 000007FF7717FFFF 00030000
* WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000
* USERENV 000007FF7C4A0000 000007FF7C5A8FFF 00109000
* opends60 00000000333E0000 00000000333E7FFF 00008000
* NETAPI32 000007FF771B0000 000007FF77247FFF 00098000
* SHELL32 000007FF7F190000 000007FF7FB97FFF 00a08000
* SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000
* comctl32 0000000000AF0000 0000000000C76FFF 00187000
* psapi 000007FF7E1B0000 000007FF7E1BFFFF 00010000
* instapi 0000000048060000 000000004806CFFF 0000d000
* sqlevn70 000000004F610000 000000004F7A3FFF 00194000
* SQLOS 00000000344D0000 00000000344D5FFF 00006000
* rsaenh 000000000FFB0000 000000000FFEDFFF 0003e000
* AUTHZ 000007FF7E2C0000 000007FF7E2ECFFF 0002d000
* MSCOREE 000006427EE60000 000006427EED3FFF 00074000
* ole32 000007FF7ECE0000 000007FF7EF51FFF 00272000
* msv1_0 000007FF7E330000 000007FF7E373FFF 00044000
* iphlpapi 000007FF7E3C0000 000007FF7E3EFFFF 00030000
* kerberos 000007FF77410000 000007FF774C2FFF 000b3000
* cryptdll 000007FF7DAB0000 000007FF7DABEFFF 0000f000
* schannel 000007FF7DB70000 000007FF7DBB0FFF 00041000
* COMRES 000007FF7E920000 000007FF7E9E5FFF 000c6000
* XOLEHLP 000007FF5C560000 000007FF5C566FFF 00007000
* MSDTCPRX 000007FF67140000 000007FF67210FFF 000d1000
* msvcp60 0000000009F50000 000000000A039FFF 000ea000
* MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000
* VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000
* WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000
* OLEAUT32 000007FF7E9F0000 000007FF7EB03FFF 00114000
* CLUSAPI 000007FF7B1C0000 000007FF7B1E3FFF 00024000
* RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000
* DNSAPI 000007FF7E720000 000007FF7E76DFFF 0004e000
* winrnr 000007FF7E820000 000007FF7E82AFFF 0000b000
* WLDAP32 000007FF7E780000 000007FF7E7E5FFF 00066000
* rasadhlp 000007FF7E830000 000007FF7E836FFF 00007000
* security 000007FF77530000 000007FF77534FFF 00005000
* msfte 0000000049980000 0000000049D2DFFF 003ae000
* dbghelp 000000000C600000 000000000C759FFF 0015a000
* WINTRUST 000007FF7E210000 000007FF7E25DFFF 0004e000
* imagehlp 000007FF7E2A0000 000007FF7E2B2FFF 00013000
* dssenh 000000000FF70000 000000000FFABFFF 0003c000
* hnetcfg 000007FF6D1F0000 000007FF6D280FFF 00091000
* wshtcpip 000007FF76FA0000 000007FF76FAAFFF 0000b000
* NTMARTA 000007FF7E2F0000 000007FF7E32BFFF 0003c000
* SAMLIB 000007FF76F80000 000007FF76F95FFF 00016000
* ntdsapi 000007FF7DAD0000 000007FF7DAF3FFF 00024000
* xpsp2res 000007FF5C210000 000007FF5C4D6FFF 002c7000
* CLBCatQ 0000000003600000 00000000036DCFFF 000dd000
* sqlncli 00000000337A0000 0000000033A5DFFF 002be000
* COMCTL32 0000000003710000 00000000037FDFFF 000ee000
* comdlg32 000007FF7D360000 000007FF7D3D6FFF 00077000
* SQLNCLIR 0000000035000000 0000000035032FFF 00033000
* msftepxy 000000000D5D0000 000000000D5F0FFF 00021000
* xpstar90 0000000053C30000 0000000053CB4FFF 00085000
* SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000
* ODBC32 000007FF63EB0000 000007FF63F16FFF 00067000
* BatchParser90 00000000520C0000 00000000520ECFFF 0002d000
* ATL80 000000007C630000 000000007C64DFFF 0001e000
* odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000
* xpstar90 000000000EB30000 000000000EB55FFF 00026000
*
* P1Home: 0000000000000000:
* P2Home: 000000000D7FE880: 0000000000000000 000000000D7FE880 0000000080041D70 0000000000000000 00010000
00000000 FFFFFADCA0F39940
* P3Home: 0000000080041D70: 00000000011600F8 0000000080040C40 0000000080040C40 0000000000000000 00000000
00000000 0000000100000001
* P4Home: 0000000000000000:
* P5Home: 0001000000000000:
* P6Home: FFFFFADCA0F39940:
* ContextFlags: 000000000010001F:
* MxCsr: 0000000000001FA0:
* SegCs: 0000000000000033:
* SegDs: 000000000000002B:
* SegEs: 000000000000002B:
* SegFs: 0000000000000053:
* SegGs: 000000000000002B:
* SegSs: 000000000000002B:
* EFlags: 0000000000010206: 005F004F004E005F 00540053004F0048 004500480043005F 004E003D004B0043 0055004E
0000004F 005200450042004D
* Rax: 0000000089193030: 0000000089192040 0000000089193090 0000000100000004 0000000000000400 00000000
89193430 00000000891933C0
* Rcx: 0000000089193350: 0000000089193030 0000000000000000 0000000000000000 0000000187E50001 00000000
89193030 0000000000000000
* Rdx: 000000008CFAC080: 00000000013A2ED0 0000000000002000 000000000000002C 000000008CFAC170 00000000
8014AE80 0000000100000010
* Rbx: 0000000080041D70: 00000000011600F8 0000000080040C40 0000000080040C40 0000000000000000 00000000
00000000 0000000100000001
* Rsp: 000000000D7FEE18: 000000000209D303 000000008036E1C0 0000000001DACF8E 0000000000000400 00000000
0162EEAA 000000000D7FEF78
* Rbp: 0000000000000000:
* Rsi: 0000000000000001:
* Rdi: 0000000080041D70: 00000000011600F8 0000000080040C40 0000000080040C40 0000000000000000 00000000
00000000 0000000100000001
* R8: 0000000000000000:
* R9: 0000000000000001:
* R10: 0000000001146370: 00000000016DC8C0 0000000001629AC0 00000000028C0F40 000000000162A640 00000000
016C36E0 000000000162A620
* R11: 0000000000000000:
* R12: 000000008CFAD3B0: 000000008CFACFB0 000000008CFAC410 000000008CFACEA0 000000008CFAD3F8 00000000
8CFAD618 0000000000000001
* R13: 0000000000000000:
* R14: 0000003600000005:
* R15: 0000000000000001:
* Rip: 0000000089193440: 0000000089192040 0000000089193090 0000000100000004 0000000000000400 00000000
89193840 0000000089193840
* *******************************************************************************
* -
* Short Stack Dump
0000000089193440 Module(UNKNOWN+0000000000000000)
000000000209D303 Module(sqlservr+000000000109D303)
000000000209FA5F Module(sqlservr+000000000109FA5F)
00000000025493B6 Module(sqlservr+00000000015493B6)
000000000281BDD7 Module(sqlservr+000000000181BDD7)
0000000001A7DD4B Module(sqlservr+0000000000A7DD4B)
000000000162822A Module(sqlservr+000000000062822A)
0000000001636D6E Module(sqlservr+0000000000636D6E)
000000000161E868 Module(sqlservr+000000000061E868)
00000000016214CD Module(sqlservr+00000000006214CD)
000000000162CC52 Module(sqlservr+000000000062CC52)
000000000162C5EF Module(sqlservr+000000000062C5EF)
000000000167EBCE Module(sqlservr+000000000067EBCE)
000000000167E3D9 Module(sqlservr+000000000067E3D9)
00000000016866F4 Module(sqlservr+00000000006866F4)
00000000014BF777 Module(sqlservr+00000000004BF777)
00000000017B4DF9 Module(sqlservr+00000000007B4DF9)
000000000179A5A0 Module(sqlservr+000000000079A5A0)
00000000017E33F8 Module(sqlservr+00000000007E33F8)
00000000781337A7 Module(MSVCR80+00000000000037A7)
0000000078133864 Module(MSVCR80+0000000000003864)
0000000078D6B66A Module(kernel32+000000000002B66A)
PSS @0x000000008CFB20F0
--
CSession @0x000000008CFAC410
-
m_spid = 54 m_cRef = 12 m_rgcRefType[0] = 1
m_rgcRefType[1] = 1 m_rgcRefType[2] = 9 m_rgcRefType[3] = 1
m_rgcRefType[4] = 0 m_rgcRefType[5] = 0 m_pmo = 0x000000008CFAC080
m_pstackBhfPool = 0x000000008CFB30F0 m_dwLoginFlags = 0x10b1e0 m_fBackground = 0
m_fClientRequestConnReset = 0 m_fUserProc = -1 m_fConnReset = 0
m_fIsConnReset = 0 m_fInLogin = 0 m_fReplRelease = 0
m_fKill = 0 m_ulLoginStamp = 56 m_eclClient = 5
m_protType = 5 m_hHttpToken = FFFFFFFFFFFFFFFF
m_pV7LoginRec 0000000000000000: de050000 02000972 00100000 00000007 140f0000 ?.......r............
0000000000000014: 00000000 e0b11000 2c010000 09040000 5e000500 ?........,.......^...
0000000000000028: 00000000 00000000 68001800 98000500 00000000 ?........h...........
000000000000003C: a2000500 ac000000 ac001200 000e0cb2 0e3ad000 ?.................:..
0000000000000050: 0e05d000 00000000 00000000 0000???????????????..............
m_pPhyConn->m_pmo = 0x000000008CFAC080
CPhysicalConnection @0x000000008CFAC2E0
m_pPhyConn->m_pNetConn = 0x000000008CFACAE0
m_pPhyConn->m_pConnList = 0x000000008CFAC3E0
m_pPhyConn->m_pSess = 0x000000008CFAC410 m_pPhyConn->m_fTracked = -1
m_pPhyConn->m_cbPacketsize = 4096 m_pPhyConn->m_fMars = 0 m_pPhyConn->m_fKill = 0
CBatch @0x000000008CFACFB0
--
m_pSess = 0x000000008CFAC410 m_pConn = 0x000000008CFACEA0 m_cRef = 3
m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 1
m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x00000000052B8718
exc_number = 0 exc_severity = 0 exc_func = 0x0000000001AF1880
EXCEPT (null) @0x000000000D7FBD08
Task @0x00000000052B8718 CPU Ticks used (ms) = 0 Task State = 2
WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0
WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0x7
ThreadId = 0x1b40 m_state = 0 m_eAbortSev = 0
EC @0x000000008CFB2100
-
spid = 54 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1
ec_dbccContext = 0x0000000000000000 __pSETLS = 0x000000008CFACF10 __pSEParams = 0x000000008CFAD330
__pDbLocks = 0x000000008CFB2540
SEInternalTLS @0x000000008CFACF10 m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x00000000052B8718
m_activeHeapDatasetList = 0x000000008CFACF10
m_activeIndexDatasetList = 0x000000008CFACF20
SEParams @0x000000008CFAD330
-
m_lockTimeout = -1 m_isoLevel = 4096 m_logDontReplicate = 0
m_neverReplicate = 0 m_XactWorkspace = 0x00000000800405F0
m_pSessionLocks = 0x000000008CFB2890 m_pDbLocks = 0x000000008CFB2540
m_execStats = 0x0000000083378600 m_pAllocFileLimit = 0x0000000000000000
