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
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...?