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

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:

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: Aug 6, 2012 - Revision: 1

Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 Enterprise

Feedback