Post reply

kill spid never completing

  • March 31, 2006 at 7:08 am

    #152875

    We are running SQL2000 SP3 and have a process that seems to get hung up to the point that we can't even view current activity from within EM.

    I'm having to query sysprocesses to find the spid id to kill.  When I issue the kill command, we get the following message:

    SPID xxx Transaction rollback in progress. Estimated rollback completion: 00% Estimated 33 Seconds.

    After a number of minutes we issue kill xxx statusonly and the same message appears...no progress.

    The only way we can get the process out is to reboot the server.

    Any ideas?

  • March 31, 2006 at 7:59 am

    #630113

    No, I would suggest you wait for it to finish.

    If a transaction was running for an hour, and you killed it, it would take about an hour (generally speaking) to rollback. As long as its showing a rollback, it is probably doing something.

    If you were to reboot now, it will cause sql server to do a recover on startup, and that could take a while also.

     

  • March 31, 2006 at 8:33 am

    #630146

    Please check DBCC Inputbuffer(<SPID&gt and see what this SPID was trying to do.

    If you see something like xp_cmdshell or some other OS call, then you may see "KILLED/ROLLBACK"

    Rebooting should not harm anything but as pointed by Ray that it may take time to recover if its doing "real" rollback

  • March 31, 2006 at 8:36 am

    #630147

    we don't think it's doing a real rollback...reboot is very quick

  • March 31, 2006 at 8:39 am

    #630149

    Would you mind checking DBCC INPUTBUFFER(SPID) to prove the theory pf "real" rollback

  • March 31, 2006 at 11:37 am

    #630206

    the developer has been checking their process and feels that the only updates were to a temporary table and changing their processes. hopefully it won't happen again.  I'll try the dbcc next time.  thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply