Sql-server – SQL Server mirrored database is stuck in recovery mode

recoveryrestoresql serversql-server-2008

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?

Best Answer

Restart the db mirroring end point:

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

--To start
ALTER ENDPOINT<Endpoint Name> 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.

Related Question