Some sessions are suspended after Mirroring Safety is set back to Full

Some sessions are suspended after Mirroring Safety is set back to Full by Jean-Nicolas BERGER

Active

Type:	Bug
ID:	775741
Opened:	1/3/2013 5:10:48 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

In a Database Mirroring configuration, if the operating mode is set to high performance asynchronous and then set back to high safety with automatic failover, then some data modifications made during the high performance phase are blocked by a DBMIRROR_DBM_EVENT wait.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

SQL Engine

Windows Server 2008 R2 Enterprise (SP1)

French

On the same operating system, install 3 database instances (INSTANCE1, INSTANCE2 and WITNESS) and SSMS. In my test, the login used to run the database engines is the local administrator.

Create a database MyDatabase on INSTANCE1. In this database, create a table :
CREATE TABLE MyTable(Id int)

Backup the database, restore it on INSTANCE2 with the NoRecovery mode.
Edit the properties of MyDatabase on INSTANCE1 to configure a mirror :
- INSTANCE1 as the Principal , port 5022
- INSTANCE2 as the Mirror, port 5023
- WITNESS as the Witness, port 5024

Open a new query on INSTANCE1 and run the following code :
USE MASTER
ALTER DATABASE MyDatabase SET WITNESS OFF
ALTER DATABASE MyDatabase SET PARTNER SAFETY OFF

Then use SSMS to Edit Top 200 Rows of MyTable on INSTANCE1.
Add a row with 1 as the value of Id.
Don't close this edit window.

Open an new query on INSTANCE1, and run the following code (replace MIRROR by the name of the server):
USE MASTER
ALTER DATABASE MyDatabase SET WITNESS = 'TCP://MIRROR:5024'
ALTER DATABASE MyDatabase SET SAFETY FULL

Go back to the Edit Row window, and change the value of the record (e.g. Id=2)

The update fails.
By running the sp_who2 procedure, we can see a process that's suspended on a TM REQUEST command. In my test, the SPID is 57.

The following query
select * from sys.dm_os_waiting_tasks where session_id=57
returns a DBMIRROR_DBM_EVENT wait_type.

Closing the Edit Row window doen't stop the session, the SPID (57) is still returned as Suspended by sp_who2.

The update should have succeded, with no persisting DBMIRROR_DBM_EVENT wait type.

X64

Hyper-V (On-Premise)

0 attachments

Posted by Microsoft on 1/14/2013 at 1:24 PM

Hi Jean-Nicolas,

thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server

Posted by Jean-Nicolas BERGER on 1/10/2013 at 12:27 PM

Hi,
Thank you Jonathan, your workaround is great because the availability of the primary database can be nearly maintained if the restart of the secondary instance is done quickly enough.
JN.

Posted by Jonathan MacCollum on 1/6/2013 at 1:24 PM

I encountered this issue today in my lab (running SQL Server 2008 R2 - CU6)... I submitted a possible workaround.

Posted by Jean-Nicolas BERGER on 1/3/2013 at 5:11 PM

Note : the problem occurs on SQL Server 2012 SP1, and already occured on SQL Server 2008 R2 SP2.

Posted by Jonathan MacCollum on 1/6/2013 at 1:23 PM

I was able to work around this issue by restarting the instance hosting the 'mirror' partner database. After this instance came back up the asynchronous transactions were able to propagate successfully to the mirror and the database went from 'synchronizing' to 'synchronized' shortly thereafter.