none
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?

    Friday, September 15, 2006 2:20 PM

Answers

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

    Friday, September 15, 2006 11:15 PM

All replies

  • I'm moving this thread to DB Engine.  They may be able to answer or point you in the right direction.
    Friday, September 15, 2006 6:53 PM
    Moderator
  • 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.

    Friday, September 15, 2006 11:15 PM
  • 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.
    Thursday, September 21, 2006 7:29 PM
  • 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

    Thursday, March 8, 2007 11:34 AM
  • 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

     

    Tuesday, March 13, 2007 6:35 PM
  • 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.

    • Proposed as answer by Eve Harrell Tuesday, January 2, 2018 8:58 PM
    Tuesday, July 3, 2007 2:51 PM

  • 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.
    Monday, May 12, 2008 9:21 PM
  • 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.
    • Proposed as answer by PeterCroeze Friday, June 21, 2013 2:24 PM
    Friday, July 11, 2008 7:14 PM
  •  

     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.

    Friday, September 19, 2008 7:28 PM
  • 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

    Tuesday, September 23, 2008 3:11 PM
  • 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!
    Friday, July 16, 2010 7:53 PM
  • Spot on. This saved me having to restart SQL in a production environment. Thank you.
    Thursday, October 31, 2013 1:29 PM
  • Thank you!  10 years later and this answer was still helpful!

    Happy New Year!

    Tuesday, January 2, 2018 8:59 PM