Share via

Need to drop a database with recovery pending flag set

  • Article

Question

Monday, September 15, 2014 1:59 AM

Stuck on this, I think the device where the log is full and I cannot free any space. The Database is in recovery pending

I just want to drop the database, I don't need or want it

Any alter database command gives me the following

ALTER DATABASE failed because a lock could not be placed on database

Any attempt to detach or delete  the database tells me it is currently in use

sp_who2 shows no users using the database

All replies (3)

Tuesday, September 16, 2014 10:18 AM âś…Answered

Hi,

You can use the following statements to bring the database online, please note that this solution will lose data. Then you can drop the database successfully.

ALTER DATABASE [DB_Name] SET  SINGLE_USER WITH NO_WAIT
ALTER DATABASE [DB_Name] SET EMERGENCY;
DBCC checkdb ([DB_Name], REPAIR_ALLOW_DATA_LOSS  )
ALTER DATABASE [DB_Name] SET online;
ALTER DATABASE [DB_Name] SET  Multi_USER WITH NO_WAIT

Thanks,
Lydia Zhang

Monday, September 15, 2014 3:04 AM

Hi,

looks like  disk I/O issue, it happened when SQL  went down unexpectedly,  please wait to come online the DB because you can't do anything untill it's in  recovery.

Also , check after rebooting the servers if you haven't done.

Thanks

EA

Monday, September 15, 2014 3:08 AM

Was this database involved in mirroring and had a issue ? 

If yes then please go ahed with below.. if not you have to investigate more...

ALTER DATABASE database_name SET PARTNER OFFRESTORE DATABASE database_name WITH RECOVERY;

Raju Rasagounder Sr MSSQL DBA