June 17, 2008 at 10:08 am
is there any way to drop a db which has had a rogue process running for a while?
Once you kill the process it enters rollback and that may run a long time (ETA tomorrow ... perhaps... maybe the day after... ) I totally dont care about the db state. I need to restore anyway. It was a
failed upgrade.
This is sql server 2005 - i know how to do it in sql 2000 but 2005 seems to preclude the ad hoc updates to the catalogs that are needed (updates to sysdatabases). Google shows how to drop the db (alter db xxx set EMERGENCY) but this will work only if the db has no rollback.
In sql 2000 i would reset the db to suspect and bounce the server (maybe into single user mode). I would then drop the bad db and reload it. With 2005, i cant seem to set a db into 'do not recover' state. The db state/status change is blocked by the rollback of the killed spid. So the database is junk. But its locked and recovery is going so i cant drop it or reload it.
I have thought of shutting down the server, renaming the mdf/ldf files, and restarting the server. i could try setting the status flags on sqlsvr.exe first to see if i can get it to bypass recovery. Done that with 2000. Do i have any other options? Is there any way to do this without restarting the server?
June 17, 2008 at 2:16 pm
check if this helps
alter database DATABASENAME set offline with rollback immediate
drop database DATABASENAME
subban
June 17, 2008 at 3:18 pm
If that doesn't work, do the shutdown. You can rename one file and the db will be suspect and then you can deal with things.
June 18, 2008 at 6:49 am
Thank you so much... with rollback immediate ... so totally not obvious but so totally useful!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply