Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.

 

I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN. This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress.

 

I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.

 

Here is what BOL says:

 

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

 

Lab:

Step 1: I ran a long running batch from SPID 52

 

Step 2: Issued a KILL using the command KILL 52

 

Step 3: Here is what I observed from SP_WHO2 52:

SPID	Status	Login	HostName	BlkBy	DBName	Command
52	SUSPENDED	abc	def	.	microsoft	KILLED/ROLLBACK

 

Step 4: Here is what I got when I ran KILL 52 WITH STATUSONLY:

spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

 

Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ran SP_WHO2 53:

SPID	Status	Login	HostName	BlkBy	DBName	Command
53	ROLLBACK	abc	def	.	microsoft	ROLLBACK TRANSAC

 

Step 6: Here is the output of KILL 53 WITH STATUSONLY :

Msg 6120, Level 16, State 1, Line 1

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

So KILL WITH STATUS ONLY cannot be used to track the progress of ROLLBACK issued by ROLLBACK TRAN. Hope this helps!

Comments

• Anonymous
  June 04, 2015
  Thank you for the post - It was very useful and informative !!