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 ??)
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?
Can you answer this question?
Write Answer1 Answers