Mirrored SQL Databases Stuck In Recovery Mode

Following a server restart, two of three SQL Server databases in a SQL Server Database Mirroring configuration were stuck in recovery mode on the primary server.

Running sys.dm_tran_locks showed a session with a resource_database_id for each of the databases stuck in recovery.

It appeared that SQL Server Database Mirroring was preventing the recovery process completing on the two databases following the server restart.

The resolution was to stop and start the database mirroring endpoints on the primary and secondary SQL Servers.  To return a list of endpoints run select * from sys.endpoints.

To stop and restart the endpoints run

ALTER ENDPOINT %ENDPOINT NAME% STATE=STOPPED

ALTER ENDPOINT %ENDPOINT NAME% STATE=STARTED

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s