Killing a rolling back Process

Question

Killing a rolling back Process

Deviprasad

Ten Centuries

Points: 1060
More actions
August 28, 2008 at 9:15 am

#382451

Hi ,
We had a job stuck for almost 4 hrs and was restricting users to login into the application. So i tried killing it. Once i killed , it went to "Killed\Rollback" state.
And it was there for another 1 hr. When i tried killing the same SPID again it says
SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
So i had to restart the services and it resolved the issue.
But my question is: Will it trigger some process if we try killing a rollbadk process?
Donot hesitate to tell if my words here are not making any sense.
Thanks
Dev

Viewing 6 posts - 1 through 5 (of 5 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

If you want to make sure your database remains in a valid, consistent state, you shouldn't kill a session that is rolling back a transaction. In most cases, if the rollback hasn't completed, the rollback will continue after the SQL service is restarted.

I have seen cases where a rollback SPID still existed even after finishing the rollback. In those cases restarting the SQL service killed the SPID and the rollback didn't continue (because it had finished). Use KILL [spid] WITH STATUSONLY to see the percentage of rollback completed.

Greg

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 2

Normally SQL will do nothing if you attempt to kill a process that is being rolled back. It will simply display a message that it is already being rolled back.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 3

if your rollback takes abnormaly long (and should have completed according to your workload knoledge) , best is to stop/start the instance :crazy:

Your db will only be back online when the rollback has been compleded, but doing it "single user" is way faster.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Deviprasad Ten Centuries Points: 1060 More actions · Answer 4

Thanks all for your help.. Appreciate it.

That answered my question.: So if rollback in continuing and we try to kill it it will not do any thing. will jsut display a mesage that Rollback in Process.

Thanks Again

Dev

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

As Greg had stated earlier it is safer to use kill with statusonly after the first kill statement has been executed. Quote from BOL;

"The same status report can be obtained by repeating the same KILL spid|UOW statement without the WITH STATUSONLY option; however, this is not recommended. Repeating a KILL spid statement may terminate a new process if the rollback had completed and the SPID was reassigned to a new task before the new KILL statement is run. Specifying WITH STATUSONLY prevents this from happening."

The last thing we want to do is accidently kill some new process right after the previous rollback had completed while we were only trying to get a status. 😉

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot