I have a database that was involved in a mirroring session and something crashed hard and the database is stuck 'in recovery' and will not allow me to drop it because it thinks the database is in use although I can't see what.
I run this and the affected database is not listed:
SELECT DB_NAME(dbid) as 'DBName'
, loginame as 'Login'
, COUNT(dbid) as 'Connections'
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
I also run EXEC sp_who2
and the database is not listed either
I run
ALTER database careplans set offline with ROLLBACK IMMEDIATE;
DROP database careplans;
and I get an error "ALTER DATABASE failed because a lock could not be placed on database 'careplans'. Try again later."
This brings back no results:
select * from sys.sysprocesses where dbid = DB_ID( 'careplans')
This gives me the 'lock could not be placed' error:
ALTER DATABASE careplans
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
This gives me an "Exclusive access could not be obtained because the database is in use." error:
RESTORE DATABASE CarePlans WITH RECOVERY
Any of the following give me the 'lock could not be placed' error:
ALTER DATABASE careplans SET SINGLE_USER WITH NO_WAIT
ALTER DATABASE careplans SET EMERGENCY;
DBCC CHECKDB (careplans, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE careplans SET online;
ALTER DATABASE careplans SET Multi_USER WITH NO_WAIT
I have no idea what else I can try...?