KILL With StatusOnly – Where Killing, isn’t. Using “KILL ## With StatusOnly” to get transaction rollback status information

I want some Moore - I KILL-ed you! Now can I go for a coffee break while you die?

“…

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;

…

…the message you'll get is this:

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

…”

From the SQL Server 2000 BOL;

“…

WITH STATUSONLY

Specifies that SQL Server generate a progress report on a given spid or UOW that is being rolled back. The KILL command with WITH STATUSONLY does not terminate or roll back the spid or UOW. It only displays the current progress report.

For the KILL command with WITH STATUSONLY option to generate a report successfully, the spid or UOW must be currently in the rollback status. The progress report states the amount of rollback completed (in percent) and the estimated length of time left (in seconds), in this form:

SpidUOW <xxx>: Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.

If the rollback of the spid or UOW has completed when the KILL command with the WITH STATUSONLY option is executed, or if no spid or UOW is being rolled back, the KILL with WITH STATUSONLY will return the following error:

Status report cannot be obtained. KILL/ROLLBACK operator for Process IDUOW <xxx> is not in progress.

The same status report can be obtained by executing twice the KILL spidUOW command without the WITH STATUSONLY option; however, this is not recommended. The second execution of the command may terminate a new process that may have been assigned to the released SPID.

…”

Let’s see, I’ve been using KILL for how many years? Sigh… Still I guess better late than never.