HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server mirrored database is stuck in recovery mode

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
mirroredsqlstuckmodedatabaserecoveryserver

Problem

I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me.

Here is what I tried already:

RESTORE DATABASE [DBNAME] WITH RECOVERY 
Error: Exclusive access could not be obtained because the database is in use.

ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
Error: ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'  
I get the same error when trying to set the db to SINGLE_USER

exec sp_who2 --> nothing that contains my database, so nothing that I can kill  
(Or I need to look for something else ??)


I can't just stop the SQL-service, because it contains too many databases that can't go down.

Who knows what I can do to get the database out of recovery mode? At both the primary and mirror location is the database state 'In recovery'.
UPDATE

I found a process in sp_who2 with command DB STARTUP.

At sys.dm_tran_locks, I see this session has a resource_database_id for the database that is in recovery, so this keeps the database locked. Anyone knows how to fix this without stopping SQL Server?

Solution

Restart the db mirroring end point:

--To stop 
ALTER ENDPOINT STATE=STOPPED

--To start
ALTER ENDPOINT STATE=STARTED


TECH note: if there are multiple databases, not just the one in error, then stopping and starting the enpoint affects all the databases on that endpoint. This can cause problems on a production system like SharePoint. To fix, go the other mirrored database server and enter the same commands:
ALTER ENDPOINT STATE=STOPPED
ALTER ENDPOINT STATE=STARTED

How to find the name of the end point?
select * from sys.endpoints <- works for SQL 2012
The name will be of " type=4 " for mirroring.

Code Snippets

--To stop 
ALTER ENDPOINT<Endpoint Name> STATE=STOPPED

--To start
ALTER ENDPOINT<Endpoint Name> STATE=STARTED

Context

StackExchange Database Administrators Q#57944, answer score: 6

Revisions (0)

No revisions yet.