Post reply

Commit the transaction which is currently rolling back

  • December 12, 2012 at 12:51 pm

    #266096

    I had a huge transaction (1.5 Billion) rows running for more than 24 hours and I see that after working on 1.2 B rows its rolloing back, how can I commit the transaction which is rolling back?

  • December 12, 2012 at 1:02 pm

    #1568046

    You cannot. Dont try killing the SPID - you may end up with a corrupt database(although SQL Server will try to respawn the rollback process).

  • December 12, 2012 at 1:08 pm

    #1568050

    You can't commit it. It has to finish the rollback.

    Trying to kill it will not cause a corrupt database (there is nothing that a user can do via T-SQL that can cause corruption), it'll just give a report on how far the rollback is.

    Do not restart SQL. If you do the rollback will finish after the restart with the database unavailable (unless you have enterprise edition)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 12, 2012 at 1:10 pm

    #1568051

    Good to know..Luckyly I was commiting transaction for each day in the loop and the data was committed

Viewing 4 posts - 1 through 3 (of 3 total)

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