Symptoms
When you use database mirroring in Microsoft SQL Server 2012 or Microsoft SQL Server 2014, you may hit an assert condition and database mirroring enters to suspended state.
Cause
The issue occurs because, when allocating a new page, SQL Server obtains an X lock on the new page. SQL Server will put the hobt_id (Heap or B-Tree ID) to which the new page belongs in the lock request. However, SQL Server cannot put the hobt_id in Mirroring Log and results in different lock behavior between the primary and mirror.
This can be explained in detail as follows:
-
T1 hold an IX lock on Page P1.
-
T2 do a page split on P1, allocate a new page P2, a system transaction TX is used here, it holds an X lock on P2. Here SQL Server did not put the hobt_id in Mirroring Log.
-
TX does a lock migration for T1 to move the IX lock from P1 to P2.
-
TX committed, now T2 can use Page P2, and T2 obtain another IX lock on page P2.
-
T1 committed, now T2 is the only one who holds an IX lock on P2.
-
After lots of inserting, a lock escalation occurs, on the primary, T2 releases the IX on P2, but on the mirror, during lock escalation, T2 did not release the IX lock.
-
After lots of deleting, Page P2 became empty and is deallocated.
-
T3 needs a new page, and it happens to allocate P2, this requires an X lock, but on the mirror, this step failed because of step 6.
On the mirror, Step 6 does not release the IX lock because the hobt_id in the lock block is incorrect. This incorrect hobt_id comes during step 2 and because of SQL Server does not put the hobt_id in Mirroring Log.
Usually you do not see any issue because the TX in step 2 is very short, and the lock block with incorrect hobt_id will be released when it commits. However, because of lock migration in step3 and the following steps (4 and 5), this lock block with incorrect hobt_id is preserved and finally causes the problem.
The primary does not have this issue because it uses a correct hobt_id in step 2. But the log record does not have correct hobt_id.
Resolution
The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 1 for SQL Server 2014 /en-us/help/2931693
Cumulative Update 9 for SQL Server 2012 SP1 /en-us/help/2931078
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Workaround
To work around the issue, reinitialize the mirror to end the suspended status.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
