SQL Server Assertion error when you try to execute a Bulk Insert or BCP statement: "<loglock.cpp>, line=807 Failed Assertion = 'result == LCK_OK' "

Article translations Article translations
Article ID: 2700641 - View products that this article applies to.
Expand all | Collapse all

Symptoms

Consider the following scenario:
  • 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.

Cause

This issue occurs because the lock compatibility information in the transaction log of the principal database is not transferred to the mirror server.

Workaround

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.

More information

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.

Properties

Article ID: 2700641 - Last Review: August 6, 2012 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 Enterprise
Keywords: 
kbsurveynew kbtshoot kbprb KB2700641

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com