I KILL-ed you! Now can I go for a coffee break while you die?
The victim of my carnage being a connection to the SQL Server 2005.
How many times have you issued a query that you had no idea how long it would run, just to loose your patience after 10 minutes and rolled it back, forgetting that it'll probably take 10+ minutes to rollback anyway?
Come on don't be shy... raise your hand... I know you're one of them :)
Although there is no way to get the rollback time from the ROLLBACK command (if there is do let me know) we can use an extended way of calling the KILL command.
KILL command has an option WITH STATUSONLY that tells you how long until will the killed process completes.
We call it like this:
So basically you can do this in one window:
And after you issue a rollback run the following in another window:
the message you'll get is this:
And after running it a few more times you can see that the times are nicely displayed:
We can see that the progress report is quite handy to have. and if it takes around 10 minutes you at least know you have time for some coffee.
So don't let those long lasting rollbacks eat your nerves. Happy KILL-ing!
Legacy Comments
AjarnMark
2008-11-03re: I KILL-ed you! Now can I go for a coffee break while you die?
D'OH! I wish I had known this several months ago! ARGH! I guess that's a good reminder to occasionally read BOL for additional options on commands you already think you know just fine.
Thanks for the whack on the side of the head. :)Mladen
2008-11-03re: I KILL-ed you! Now can I go for a coffee break while you die?
you're welcome. :)Adam Machanic
2008-11-04re: I KILL-ed you! Now can I go for a coffee break while you die?
Is the STATUSONLY option really needed? I think it's the default when the SPID is already in the process of being killed/rolled back. Alas, most of the time when I really do need to kill something it shows 0% for the whole time. You can also query this data from sys.dm_exec_requests, by the way (percent_complete and estimated_completion_time).Mladen
2008-11-04re: I KILL-ed you! Now can I go for a coffee break while you die?
thanx for the DMV tip adam. did not know that!tosscrosby
2008-11-05re: I KILL-ed you! Now can I go for a coffee break while you die?
Are the time/percent estimates relatively accurate, in your opinion? I've seen some of Microsoft's estimates, like when copying files across the network, spike out to some un-Godly number like 4,318 minutes to complete and in reality it completes in maybe 5 minutes. Or maybe even worse, it shows 5 minutes and really does take 7 hours!!!Mladen
2008-11-05re: I KILL-ed you! Now can I go for a coffee break while you die?
well the ones i've seen have been preety accurate. more or less...Gail
2008-11-07re: I KILL-ed you! Now can I go for a coffee break while you die?
> Alas, most of the time when I really do need to kill something it shows 0% for the whole time.
I've had the same thing. I killed a Merge statement that had been running for quite some time (hours). Report was 0% complete, 0 seconds remaining. I rebooted the server (hardware reconfig, dev server) and once SQL started up it spent the next 14 hours in recovery doing the rollback. Fortunately it was an online recovery.Manoj
2009-01-06re: I KILL-ed you! Now can I go for a coffee break while you die?
HI,
I killed a process which had been running for days. Now it is in the KILLED/ROLLBACK status. But I am seeing that the Estimated Time Remaining is INCREASING instead of going down.. Any ideas? Anything really fatal?
Manoj.Jeff Bennett
2009-01-23re: I KILL-ed you! Now can I go for a coffee break while you die?
I am seeing this situation where killing the process results in either 0% completed and 0 seconds left (which displays forever), or the XX% with increasing times remaining. I was trying to kill an incremental backup, and a transaction log backup at the time, because both were seemingly hung, and I was getting 1450 errors (INSUFFICIENT RESOURCES).
I think at this point, we reboot, which stops the hung ROLLBACK's and addresses the 1450 errors for a short while.
Jeff