locked
Dropping a RECOVERING database RRS feed

  • Question

  • Hi,

    Is there any way(better) to drop a RECOVERING database?

    We tried to change a db from READ ONLY state to READ/WRITE state and this took forever (db is very small 40 MB) and we tried to kill this process and this went to KILLED/ROLLBACK state and blocking other processes.

    We tried to even stop the sql server but that didn't work. We had to reboot the windows server. Now that particular database is in 'RECOVERING' state and we want to get rid of it. We've a backup of it.

    Any ideas how to drop this database?

    Because of this RECOVERING database, SQL Agent is not starting. The Agent error log says, it's waiting on SQL server to complete the recovery.

    We've SQL 2005 with service pack1.

    Any ideas are highly appreciated.

    Thanks,

    Siva.

     

    Tuesday, October 3, 2006 1:02 AM

Answers

  • You can stop sqlserver service. Rename the log file. Restart sqlserver service - this will put the database into suspect mode. You should be able to drop the database now.
    (do be sure you have a good backup before forcing the deletion outlined above).
    Tuesday, October 3, 2006 6:33 AM

All replies

  • You can stop sqlserver service. Rename the log file. Restart sqlserver service - this will put the database into suspect mode. You should be able to drop the database now.
    (do be sure you have a good backup before forcing the deletion outlined above).
    Tuesday, October 3, 2006 6:33 AM
  • Thanks........that worked....

    Still don't understand why it took so long to change from READ ONLY to READ/WRITE and also why it took so long for rollback as well.

     

     

    Tuesday, October 3, 2006 2:19 PM
  • We did see in the error log the STACK DUMP thing and the following message many times:

     

    Process 71:0:0 (0x69c) Worker 0x78FF80E8 appears to be non-yielding on Scheduler 2. Thread creation time: 12804246994487. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 98%. Interval: 7331203 ms.

     

    71 was the process we killed but was unyielding.....

    Tuesday, October 3, 2006 2:57 PM
  • I've 4 .mdmp files each is about 2.5 MB.

    I don't know how I can post it here...

    Do you need any specific info?

     

    Tuesday, October 3, 2006 5:28 PM
  • What's your e-mail address?

     

    Is it 9aeb4aec-aa39-43b3-9ab2-658e5d06f246@discussions.microsoft.com ?

    or ?

    Tuesday, October 3, 2006 6:32 PM