locked
Need to drop a database with recovery pending flag set RRS feed

  • Question

  • 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

    Monday, September 15, 2014 1:59 AM

Answers

  • 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

    Tuesday, September 16, 2014 10:18 AM

All replies

  • 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:04 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 OFF

    RESTORE DATABASE database_name WITH RECOVERY;


    Raju Rasagounder Sr MSSQL DBA

    Monday, September 15, 2014 3:08 AM
  • 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

    Tuesday, September 16, 2014 10:18 AM