SQL Server mirrored database is stuck in recovery mode

Question

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?

Answer 1

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.

Answer 2

I did this and waited.

ALTER DATABASE <datbasename>  SET PARTNER OFF;

it actually worked and I could run:

RESTORE DATABASE  <datbasename>  WITH RECOVERY;

Answer 3

Do you mind restoring from a backup?

You could try to use KILL on the session, but that might mess up the database integrity.

Answer 4

You might just be at that point where you need to failover and schedule an outage/wait it out.

Not the ideal solution, but it happens :/

Answer 5

When you see any database is in restoring mode, in SQL Server 2014 try to execute the following statement from master database.

restore database <dbname> with recovery

Answer 6

Today I have faced same problem. while configuring mirroring in SQL Server 2012 I observed that my mirror database status is in recovery mode. What I did is

alter database database_name set partner off

Now my mirror database status is Restoring mode.

Answer 7

Following worked for me on the Principal Server.

ALTER DATABASE <datbasename>  SET PARTNER OFF;
RESTORE DATABASE  <datbasename>  WITH RECOVERY;

Unable to get the database out of "in recovery" state on the mirrored box. Tried to: drop the database put it in emergency mode put it in single user mode put it offline

nothing worked, got following errors:

"Only user processes can be killed. blah blah" "Database is in use blah blah"

I have stopped & started SQL Server service and "Mirroring Endpoint" on the mirrored instance but didn't fix the issue. Still showing the database "in recovery" state.

Solution: I have stopped SQL Server service and deleted the log (.ldf) file of the database. Started SQL Server service and surprisingly it worked; the database was online. I have dropped the database and reconfigured mirroring.

Answer 8

Case: Accidently When u run below command on Principal server instead of mirror server: alter database 'Database_Name' set partner off

After running above command your DB goes in [mirror, disconnected] State on mirror server.

Resolution:

Step 1:Run below query on mirror exec sp_resetstatus 'Database_Name'

Step 2: After running below command your DB will be available. restore database 'Database_Name' with recovery

Note: Step 2 will take some time to recover.