FIX: The database sticks in role configuration phase when you perform a manual failover of a mirrored database

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard More

Symptoms


Assume that you use Microsoft SQL Server 2012 or SQL Server 2014. When you perform a manual failover of a mirrored database, you may encounter the following issues:
  • The database takes a long time to recover and to be online on the new Principal server.
  • The database stays in "In Recovery" status for a long time and does not take on the role of the mirror database on the new mirror server. At the same time, the new Principal database is in "Principal, Disconnected" state.

Cause


The issue occurs because the new connection encounters a race condition.
Any new connection should try to obtain a shared lock to the database before actually starts executing queries against it. When a database exclusive lock is held because of an internal operation, such as database recovery for mirroring, new connections may encounter a race condition where the database is open to accept connections at the time mirroring failover starts. At this moment, the new connection will try to obtain a shared lock while the exclusive lock is held by mirroring recovery task. This problem occurs when the database mirroring is not proceeding any longer because the max number of workers is reached and occupied by the connection tasks.

Resolution


This fix introduces a new trace flag 3448. This trace flag forces new connections to keep checking for database state every two seconds instead of waiting for a lock for infinite time. It helps ending the connection tasks faster as the mirroring reaches the start of the recovery phase and releasing more worker threads to be used by database mirroring.
The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 4 for SQL Server 2014

Cumulative Update 2 for SQL Server 2012 SP2

Cumulative Update 11 for SQL Server 2012 SP1

Status


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