How to Stop SQL Server Agent Job while it is actively running?

How to Stop SQL Server Agent Job while it is actively running?

Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.

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 ranSP_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!

Reference: Sakthivel Chidambaram