How to kill Rollback process

Question

How to kill Rollback process

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55544 More actions · Answer 1

Gedas (10/8/2010)
You can kill rollback only by doing that but you must have latest backup:
1. stop SQL engine
2. delete db and tlog files
3. start SQL engine
4. delete db
5. restore db

Wow ! That's a pretty drastic "solution" :w00t:

I think those actions would leave you worse off than before, especially in a production environment.

Gedas SSC Enthusiast Points: 163 More actions · Answer 2

Yep, it's worst-case scenario but it's working.

And sorry I didn't see SSC Veteran reply.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

homebrew01 (10/8/2010)
Gedas (10/8/2010)
You can kill rollback only by doing that but you must have latest backup:
1. stop SQL engine
2. delete db and tlog files
3. start SQL engine
4. delete db
5. restore db
Wow ! That's a pretty drastic "solution" :w00t:
I think those actions would leave you worse off than before, especially in a production environment.

I think you'd want to do a tail log backup before dropping the DB, or you'll lose data...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gedas SSC Enthusiast Points: 163 More actions · Answer 4

I wouldn't do it in production environment at all. I was having in mind staging environment when you do big db structural change using one transaction.

cppprogrammer Right there with Babe Points: 797 More actions · Answer 5

Gedas (10/8/2010)
You can kill rollback only by doing that but you must have latest backup:
1. stop SQL engine
2. delete db and tlog files
3. start SQL engine
4. delete db
5. restore db

Thanks. This also is handy when you are prototyping large schema or data migrations and underestimate resource requirements. Sometimes the big sledgehammer is needed and start from a fresh backup!

Tim Shay SSC Journeyman Points: 75 More actions · Answer 6

GilaMonster - Thursday, March 27, 2008 1:33 PM
SQL Noob (3/27/2008)
Michael Earl (3/27/2008)
You cannot - if you were able to kill a rollback, it would leave your database in an inconsistent state. If you stop and start the SQL service, it will continue the rollback when it starts.You must simply wait for it to finish.
sometimes we get a rollback get hung up and the only way to kill it is to restart SQL or reboot. never had an inconsistent db in this case
No, because SQL finished the rollback after the server restarts. What Michael's saying is if there was a way to cancel a rollback (which there isn't) you could get a transactionally inconsistent DB.

It's a bummer, really. In my case I don't even care if the db is thrashed, unusuable, whatever. I just want the process dead so it'll stop eating all my IO - but I want that without having to take down other databases on the same box.

hkamal.infoedge SSC Eights! Points: 873 More actions · Answer 7

Sorry, It is a Pain !!.But unfortunately you have to wait for the roll Back to finish.Even if you restart the SQL your db will not be accessible until it is rolled back.