You may encounter a 'result == LCK_OK' assertion on a SQL Server mirror server

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 R2 Datacenter

Symptoms


In Microsoft SQL Server mirroring architecture, you may encounter a SQL Server assertion on the partner (mirror) server. Additionally, you find an error message that resembles the following in the SQL Server error log. This error usually means that the mirror pair must be rebuilt.
SQL Server Assertion: File: loglock.cpp, line=834 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.

Error: 3624, Severity: 20, State: 1.

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.

Note When this issue occurs, a mini-dump file is generated in the SQL Server error log folder. This file has a name that resembles "SQLDumpnnnn.mdmp."

Cause


This issue may occur in several different scenarios. Each scenario has a different cause and resolution, and each scenario may cause the same error message and assertion to occur.

Notes
  • Although the error signature seems to be very specific, the actual error is caused by an assertion that failed. For example, the error could be caused by an assertion that performs a proactive check in the SQL Server code that validates "healthy"' conditions in order to fail as cleanly as possible instead of causing a process-wide crash.
  • You cannot easily determine the actual cause. Microsoft Customer Support Services usually determines the cause. It usually does this by collecting the principal database full backup file and the transaction log backups that cover the time of the issue. Additionally, a full process dump file of the mirror may be required to reproduce the problem in specific settings.

Resolution


Service pack information

To resolve this issue, obtain the latest fix for your version of SQL Server. For more information, refer to the following table.

CauseKnowledge Base articleFirst fixed in
Different lock behavior between the primary and mirror2938828 FIX: Database mirroring hits assert and mirroring session shows suspended state in SQL Server 2012 or SQL Server 20142931693 Cumulative Update 1 for SQL Server 2014

2931078 Cumulative Update 9 for SQL Server 2012 SP1
Lock escalation issues953625 FIX: Error message when SQL Server 2005 synchronizes a mirrored database: "Expression: result == LCK_OK"951217 Cumulative update package 8 for SQL Server 2005 Service Pack 2

Lock migration during page splits974319 FIX: Intermittent error message and assertion failure when you use database mirroring in SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2: "SQL Server Assertion: File: <loglock.cpp>, line=823 Failed Assertion = 'result == LCK_OK'"974648 Cumulative update package 6 for SQL Server 2005 Service Pack 3

975976 Cumulative update package 8 for SQL Server 2008

975977 Cumulative update package 5 for SQL Server 2008 Service Pack 1

975976 Cumulative Update package 1 for SQL Server 2008 R2

Bulk Insert / BCP with Check_Constraints OFFSQL Server 2012
Change of the encryption keys:
  • Database master key
  • Server instance master key
SQL Server 2012
Shrink Operations (PurgeIAM) conflicting with other transactions982933 Error message when you shrink data files on principal in a Database Mirroring for two SQL Server 2005 servers983329 Cumulative update package 10 for SQL Server 2005 Service Pack 3

Page split followed by lock escalation followed by deallocation and an allocation of that page983564 FIX: Error message when you use database mirroring in Microsoft SQL Server 2005, Microsoft SQL Server 2008, or Microsoft SQL Server 2008 R2, and an assertion failure occurs intermittently983329 Cumulative update package 10 for SQL Server 2005 Service Pack 3

2083921 Cumulative update 9 for SQL Server 2008 Service Pack 1

2289254 Cumulative update 1 for SQL Server 2008 Service Pack 2

2261464 Cumulative update package 3 for SQL Server 2008 R2


Notes
  • The last column lists only the first build that contains the fix. Because SQL Server builds are cumulative, later builds such as SQL Server 2008 R2 SP1 contain those fixes. However, those builds are not listed in the table.
  • Any build later than SQL Server 2005 SP3 Cumulative Update (CU) 10, SQL Server 2008 SP2 CU 1, or SQL Server 2008 R2 CU3 contains all the listed fixes for "lck_ok" assertion scenarios. At the time this article was written, those builds were superseded by at least one full service pack. The service pack should protect most installed up-to-date builds of SQL Server in the fixed scenarios.

    For more information about how to obtain the latest service pack for your version of SQL Server, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    913089 How to obtain the latest service pack for SQL Server 2005

    968382 How to obtain the latest service pack for SQL Server 2008

    2527041 How to obtain the latest service pack for SQL Server 2008 R2
  • You should be aware that two scenarios do not have available fixes. The reason is that those two issues require a re-architecture of SQL Server transaction log internals. Such a change can be included only with a major release of SQL Server. In this case, the issue is fixed in Microsoft SQL Server 2012.

    The master key change scenario is a rare enough event to make the scenario a corner case. However, the BCP/Bulk Insert scenario is common. Because the BCP/Bulk Insert scenario remains unfixed for SQL Server 2008 and SQL Server 2008 R2, it is the most probable known cause of "lck_ok" assertions on the current builds of these products. For more information about this scenario, see the "More Information" section.
  • This article may not reflect issues that were discovered after its publication. You can search the Microsoft Knowledge Base for any new individual "Fix" article as applicable.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information


BCP/Bulk Insert scenario

A 3624 error together with a "result == LCK_OK" assertion may occur on the mirror partner of a SQL Server 2008 or a SQL Server 2008 R2 mirroring set if the following conditions are true:
  • A BCP or Bulk Insert activity is occurring on the principal database.
  • The BCP/Bulk insert activity uses the CHECK_CONSTRAINTS = OFF option.

    Note The default value for this option is OFF.

To work around this problem, set the CHECK_CONSTRAINTS option to ON for the BCP/Bulk Insert operation. The performance of the BCP/Bulk Insert operation may be affected.

Note SQL Server 2012 is not affected by this issue.

References


For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates