patternsqlMinor
SQL Server mirrored database is stuck in recovery mode
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:
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
At
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:
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.
--To stop
ALTER ENDPOINT STATE=STOPPED
--To start
ALTER ENDPOINT STATE=STARTEDTECH 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=STARTEDContext
StackExchange Database Administrators Q#57944, answer score: 6
Revisions (0)
No revisions yet.