locked
Transaction in Rollback RRS feed

  • Question

  • On my warehouse server (SQL 2008 64-bit sp, a transaction was killed yesterday and has been in rollback status since (close to 24 hrs so far).

    Is there any way for me to tell how much longer it'll take to finish rolling back? 

    When I use sp_whoisactive it will only show me NULL for Percent_Complete for this process.

    I'm also seeing a fair amount of waits for LATCH_EX[LOG_MANAGER] which I'm assuming is related to the transaction log expanding.

    Is there anything I can do to help that process along and make it go a little faster? 

    thank you,

    Toni

    Wednesday, May 16, 2012 2:33 PM

Answers

  • No just let it finish, do not restart or shut down your server

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Toni2 Wednesday, May 16, 2012 2:51 PM
    Wednesday, May 16, 2012 2:34 PM
    Answerer
  • The only thing you can do is KILL <spid> WITH STATUSONLY, which will give you the status of the rollback (kinda).  If the log is where your bottleneck is there isn't a whole lot that you are going to be able to do about it while a transaction is in rollback status because the VLFs internally are all going to be active.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Toni2 Wednesday, May 16, 2012 2:51 PM
    Wednesday, May 16, 2012 2:39 PM

All replies

  • No just let it finish, do not restart or shut down your server

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Toni2 Wednesday, May 16, 2012 2:51 PM
    Wednesday, May 16, 2012 2:34 PM
    Answerer
  • The only thing you can do is KILL <spid> WITH STATUSONLY, which will give you the status of the rollback (kinda).  If the log is where your bottleneck is there isn't a whole lot that you are going to be able to do about it while a transaction is in rollback status because the VLFs internally are all going to be active.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Toni2 Wednesday, May 16, 2012 2:51 PM
    Wednesday, May 16, 2012 2:39 PM
  • Thanks.  That makes me feel a little better.  I've been telling the users that but I was beginning to wonder if there was possibly something I could do.

    I've been checking the status of that process and all I've been able to see (even since yesterday when it was killed) was:

    Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

    The users have learned a valuable lesson on this one.

    We'll wait it out.

    Toni

    Wednesday, May 16, 2012 2:50 PM