May 22, 2002 at 6:36 pm
Is it possible to drop a database that is flagged as "RECOVERING" in Enterprise Manager - this was due to some big jobs I ran which was taking too long and I eventually killed them. The Recovering seems to be going on forever and I wanted to simply drop it and do a restore. I can't issue Drop Database because it's in use.
May 22, 2002 at 8:38 pm
If its the result of a restore, you can change the state by running RESTORE DATABASE MyNwind WITH RECOVERY. If you're waiting database recovery such as occurs after a bad shutdown, you'll have to wait it out. Worst case if you can stop the service it should clear.
Andy
May 23, 2002 at 5:37 am
Did it clear or you find another way around? If not then this may work, stop SQL server, rename the files for the DB, restart SQL. Now when you view your database you will see it is marked suspect. Now do your restore over the top of it from yuor backup. Then you can delete the fiels you rename or move to another server to work with.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 8:38 pm
It eventually get cleared and the database was back to normal.
This was how the db got into the "recovery" state:
I shutdown SQLServer trying to kill some rollback event which was taking too long. As soon as I restarted the server, the db was marked as recovery. I guess it was still trying to resume with the rollback causing the db to be in recovery state.
Thanks for your recommendations. I'll know how to handle similar situations in the future.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply