Post reply

OOPs Killed a rollback process!! ... HELP!

  • September 29, 2004 at 10:32 am

    #86429

    SQL Analyzer SQL Canceled:

    Delete Table

    where Year > 2004

    I accidently killed the process that was doing the roll back.  Now the system is slow and I have a process called 'KILLED/ROLLBACK.  What do I do? to get this process to go away and stop tying up system resources?

     

  • September 29, 2004 at 11:14 am

    #524815

    1 of 3 options presents itself

    1.  Wait and the process will eventually go away

    2.  Stop/Restart SQL service and hope that it goes away

    3.  Reboot SQL server and hope the killed\rollback doesn't come back



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 29, 2004 at 12:36 pm

    #524823

    I've had that experience and AJ's suggestions are the only thing that I know. I'd like just add to that is, sometimes, #2 will take long time to restart due to roll back/forward step while SQL Service is starting. The easiest way to check if it is okay to start using SQL is to read the SQL Log to make sure all the database recovery is completed. Through EM is a little hard so I use store proc "master..xp_readerrorlog" to read the errorlog.

    Like AJ said, Good Hunting

  • September 29, 2004 at 12:43 pm

    #524825

    I think you should just wait.  "Killed/Rollback" in your processes means that the process WAS killed and it IS rolling back.  You really shouldn't stop the rollback.  Could wind up with a suspect database.

    Steve

  • September 30, 2004 at 2:02 am

    #524876

    I'd agree with everything mentioned so far, normally the only way to get round this is to stop/start SQL, it used to happen quite often till we changed the way we did some things (I think the technical term is a zombie process, ie. one that you cant kill).

    You can find out slightly more information by using the 'with statusonly' command for the spid.

    kill <spid> with statusonly

    If it's going to ever finish then the information you get from the command will let you know how long the system estimates it will take.

    Hope this helps,

    Mike

  • October 1, 2004 at 11:14 am

    #525065

    I have the same problem as Mike, the worse part is I do a "Kill [spid] WITH STATUSONLY" and it said it needs 188246 second (approx. 52 hours).  Apparently I don't want to wait that long, I don't know if I can just drop the table in the middle of rollback, because this is just a user-defined error table.

  • October 1, 2004 at 11:54 am

    #525071

    Hi Mike,

    that is great commend to know. Thank you..

    One note that, it is not always accurate though. I have one Killed/Roll back process that has been there last over six month and if I run that commend, I get "SPID 74: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." but it is still there.

    It doesn't happen often but when it happens only way that I was able to solve was to stop and restart the sql service.

    At least now I know if it is safe to restart the service or not.

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

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