Cannot Kill Process stays in KILLED/ROLLBACK status

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?

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

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.

|||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.|||

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

|||

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

|||

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.