Cannot Kill Process stays in KILLED/ROLLBACK status

Question

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

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

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

Answer 1

I'm moving this thread to DB Engine.  They may be able to answer or point you in the right direction.

Answer 2

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

Answer 3

I'm having the same problem.  You could have just restarted the SQL Service without restarting the server.  I had this problem once before and it happened again today.  Restarting the service fixed the problem.

Answer 4

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

 

regards

Jag

Answer 5

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

 

Answer 6

Top post Jag

 

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Answer 7

I am a daily victime of this rollback process.  Now you suggest "do not run the KILL SPID within the SQL Server instead kill the process at the OS level"

Can you explan the process from OS side........please.


Thanks in advance

Sikander R.

Answer 8

This can happen with batches that affect dbs across servers. After running KILL SPID, restarting the Distributed Transaction Coordinator breaks contact with the other server and finishes the kill.

Answer 9

 

dnabeggar wrote:

This can happen with batches that affect dbs across servers. After running KILL SPID, restarting the Distributed Transaction Coordinator breaks contact with the other server and finishes the kill.

 

Restarting DTC finished the kill for me.

Answer 10

Hi all!

 

Can I have any problem when I Restarting DTC ?

 

I work with SQL Server Enterprise 2000 using Microsoft Advanced Server 2000 in cluster.

 

 

 

Thanks,

Alejandra

Answer 11

That was my problem!  Someone must have really screwed up some code, the SQL service account had 15 cmd.exe processes running and a ton of rollbacks frozen for days.  Thanks!

Answer 12

Spot on. This saved me having to restart SQL in a production environment. Thank you.

Answer 13

Thank you!  10 years later and this answer was still helpful!

Happy New Year!