Server A and Server B are running Microsoft SQL Server 2008 or SQL Server 2008 R2.
You set up database mirroring between Server A and Server B.
You execute a BULK INSERT or BCP statement on the principal database.
Note By default, the CHECK_CONSTRAINTS option is set to off when you execute a BULK INSERT or BCP statement.
The database mirroring is broken, and the database mirroring session enters the SUSPENDED state.
In this scenario, an assertion occurs on the mirror server. Therefore, a mini-dump file is created in the SQL Server log folder. Additionally, you see following error in the SQL Server error log on the mirror server:
date time spid Using 'dbghelp.dll' version '4.0.5' date time spid **Dump thread - spid = 31, EC = 0x00000007F9B1C1A0 date time spid ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
date time spid * *******************************************************************************
date time spid * date time spid * BEGIN STACK DUMP: date time spid * 01/04/12 16:46:21 spid 12100 date time spid * date time spid * Location: loglock.cpp:807 date time spid * Expression: result == LCK_OK date time spid * SPID: 31 date time spid * Process ID: 2228
date time spid Error: 17066, Severity: 16, State: 1.
date time spid SQL Server Assertion: File: <loglock.cpp>, line=807 Failed Assertion = 'result == LCK_OK'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
date time spid Error: 3624, Severity: 20, State: 1.
date time spid A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
date time spid Error: 1454, Severity: 16, State: 1.
date time spid Database mirroring will be suspended. Server instance '<Instance name>' encountered error 3624, state 1, severity 20 when it was acting as a mirroring partner for database '<database name>'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
Note You must reinitialize database mirroring to resolve this issue.
This issue occurs because the lock compatibility information in the transaction log of the principal database is not transferred to the mirror server.
To work around this issue, execute the BULK INSERT or BCP statement on the principal database by using the CHECK_CONSTRAINTS ON option.
Note The CHECK_CONSTRAINTS ON option causes slower performance. However, the lock assert on the mirror server does not occur.
During a BULK INSERT or BCP operation, a child transaction turns off the CHECK_CONSTRAINTS option. This child transaction uses a lock that is compatible with the parent transaction locks. The compatibility information is stored in the transaction log of the principal database. Therefore, the child transaction lock request is granted on the principal database.
However, this compatibility information is not transferred to the mirror server. Therefore, the child transaction lock request is incompatible with the parent transaction locks on the mirror server. This scenario causes the assert on the mirror server.