SQL SERVER MIRRORED DATABASE IS STUCK IN RECOVERY MODE

Jayden Bell

Total Post:110

Points:774
Posted by  Jayden Bell
 1867  View(s)
Ratings:
Rate this:
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?
  1. Mayank Tripathi

    Post:397

    Points:3117
    Re: SQL Server mirrored database is stuck in recovery mode

    Restart the db mirroring end point:

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

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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!