Resolving the 'KILLED/ROLLBACK' sessions in SQL Server

Hi Folks,

One of the not so frequent issues encountered off lately which doesn't has any other solution than to restart the SQL Service ( Recycling ) is when you have the Command status as 'KILLED/ROLLBACK' for any of the sessions in sysprocesses.

The Command of the sysprocesses session 'KILLED/ROLLBACK'  happens when there's any DML ( INSERT , UPDATE, DELETE ) activity happening against any database and it is being interrupted or killed before the process is complete.

Some of the scenarios where u might encounter this situation :-

1.    Database backup using any third party software is taking longer than expected and the process is being killed. This session will lead to 'Killed/Rollback' status.
2.    Any DML statement running via linked server connecting from another server and the process is being killed from either end will lead to the 'Killed/Rollback' Status.
3.    Any DMLs being killed from the application end before the process completes, will lead the process to 'Killed/Rollback' State.

To determine which process is hung or in the state of 'Killed/Rollback' , You can use the following commands :-

SP_Who2

OR

select spid, kpid, login_time, last_batch, status, hostname, nt_username, loginame  from sys.sysprocesses where cmd = 'KILLED/ROLLBACK'
After figuring out the spid and kpid, run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

There are a few blogs recommending to kill the kpid session from the task manager.
** Please be careful about what you are doing. This is not recommended, especially,If there's any third party tool involved in the process. You may hang the third party tool's service as well, while trying to kill this session from the task manager**.

You can use this technique of killing the kpid to release the session, If there's any stored procedure or a batch running against the database directly and due to some reasons it was killed and the session moved to 'Killed/Rollback' status, that should work., However, that's never going to be the case.

So, we can try using the below command to see the status of the session.

KILL  <SPID>  with STATUSONLY

If you would get the output message as follows :-
   
SPID <spid> : transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Only if you find the percentage complete is any number other than 0% or 100% and the time remaining is other than 0 or 554 seconds, only then, you still have a hope of getting this command completed by the time displayed, else, this process can go on hung until you restart the SQL Server service.

If there's a linked server or replication process involved in this session, here's what we can do :-

Try restarting the DTC ( Distributed Transaction Coordinator) service, which could release the session from 'Killed/Rollback' Status.

This could work if there's any Linked Server or Replication service involved in the process, OR, if there's any application triggered stored procedure which was closed from the front-end before the transaction committed.

Considering this issue occurring on the Production server where you would not have the leverage of restarting the SQL Server Service frequently.

There's one more option which you could try before giving up or concluding to restarting the SQL Server service :-

KILLING THE PROCESS USING THE Dedicated Administrator's Connection (DAC).

You need to have the DAC enabled / activated before your can think of using this option.

To know more about how to connect using DAC and how to check if the DAC is enabled, Please check the post on DAC ;-
http://sqlskillport.blogspot.in/2012/03/how-to-connect-to-dedicated.html

So, if we have the DAC enabled and if you are able to connect to the DAC session, Please go ahead and run the appropriate queries to determine the spids associated with the command 'KILLED/ROLLBACK' and kill them. This could resolve the issue.

If the above said scenarios or options didn't work for you, please don't think anything else, go ahead and have the SQL Service restarted to release these sessions.

Hope this helps

~Cheers,
Deepak.P.