I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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:

KILL 60 WITH STATUSONLY;

 

So basically you can do this in one window:

SELECT @@SPID as SpidToKill -- remeber this. It was 60 in my case BEGIN TRAN -- our really long query ROLLBACK

And after you issue a rollback run the following in another window:

KILL 60 -- 60 was the remembered @@SPID from the above KILL 60 WITH STATUSONLY;

the message you'll get is this:

/* spid 60: Transaction rollback in progress. Estimated rollback completion: 2% Estimated time left: 130 seconds. */

 

And after running it a few more times you can see that the times are nicely displayed:

/* SPID 60: transaction rollback in progress. Estimated rollback completion: 7%. Estimated time remaining: 92 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 18%. Estimated time remaining: 81 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 29%. Estimated time remaining: 71 seconds. SPID 60: transaction rollback in progress. Estimated rollback completion: 94%. Estimated time remaining: 3 seconds. -- we get this message after the spid has completed dieing Msg 6106, Level 16, State 1, Line 2 Process ID 60 is not an active process ID. */

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!

 

kick it on DotNetKicks.com
 

Print | posted on Monday, November 03, 2008 10:04 AM |

Feedback

Gravatar

# re: 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. :)
11/3/2008 7:55 PM | AjarnMark
Gravatar

# re: I KILL-ed you! Now can I go for a coffee break while you die?

you're welcome. :)
11/3/2008 7:56 PM | Mladen
Gravatar

# re: 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).
11/4/2008 3:45 PM | Adam Machanic
Gravatar

# re: 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!
11/4/2008 6:34 PM | Mladen
Gravatar

# re: 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!!!
11/5/2008 7:22 PM | tosscrosby
Gravatar

# re: 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...
11/5/2008 10:00 PM | Mladen
Gravatar

# re: 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.
11/7/2008 9:27 PM | Gail
Gravatar

# re: 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.
1/6/2009 1:15 PM | Manoj
Gravatar

# re: 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
1/23/2009 4:29 PM | Jeff Bennett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET