SQL Server - Kill a KILLED/ROLLBACK status process without restarting Server or SQL services?

This may be the typical situation within your database environment where the you might have killed a SPID (Process) that has been running  for long time, without knowing the ROLLBACK operations for such processes.

Say if you are executing a stored procedure which is built with a transactional based statements, by default the relational engine has the ability to control transactions mainly when you have specified a transaction starts and ends. In addition to that it must also be able to correctly handle errors that terminate a transaction before it completes, as in this case the transactions are managed at the connection level. Going back to the basics, say when a transaction is started on a connection all TSQL statements executed on that connection are part of the transaction until the transaction ends. But when you have multiple active results set such as MARS sessions, then that TSQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. Once the batch execution is completed and if that batch-scope transaction is not committed or rolled back, then it is automatically rolled back by SQL Server.

Also these transaction modes are managed at the connection level. If one connection changes from one transaction mode to another, it has no effect on the transaction modes of any other connection. For further error handling within that nested transactions level you can effectively include the TSQL TRY…CATCH construct, in this case Error Handling in SQL Server – a Background is the favourite among the SQL world, refer to it to solve your relevant issues. By default within those TSQL statements or stored procedure execution if you haven't specified the savepoint_name or transaction_name  within your ROLLBACK statement then it will roll back all the statements that were executed within the outermost BEGIN TRANSACTIOn statement.

Coming to the subject of killing a long running process, before doing that ensure to check what that process is executing by uisng DBCC INPUTBUFFER and if you don't get complete information of that process, then refer to Dig-Further_DBCC_INPUTBUFFER  process here. Also ensure to check whether any changes happening on CPU & IO against that SPID when you execute SP_WHO2 to get information. If it is compulsory to kill that SPID then ensure to execute KILL spid WITH STATUSONLY to see the rollback progress. Say if you have execute the above specified statement then you will see the information as follows :

SPID xx: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Bear in mind that as the rollback process will also take same time as that query execution or even more than that due to the underlying transaction log process that needs to be completed. It is a general assumption by the users that if you use SIMPLE recovery model on database it will take care the log sizes, well it is not. It will truncate the log at every checkpoint but it will not when the rollback transaction has to fillup 60% of the current log size then it will wait to increase the Transaction log file for that database which will have slow process affect to get this process complete in addition to the current processes that are executing, hence the ROLLBACK process is not changing or you might see the blocking too.

So in order to kill the ROLLBACK status process without restarting SQL Server services or reboot the server the only way is to find the kill the KPID of that SPID by running :

--SQL 2005 

select * from sys.sysprocesses

or

--SQL 2000

select * from sysprocesses