Post reply

Please Advise - rollback of long running transaction

  • March 19, 2012 at 1:23 am

    #143226

    Hi Guys,

    Batch Job executed a long-running delete sql in the management studio.

    The batch job delete a huge amount of records and running for about 8 hours.

    At the 8th hours, the developer kill the session which result in killed/rollback status. I believe DB is rolling back the transaction.

    We monitor the status and it has been the same status for 2 hours. We tried to bring the database offline but not able to as the session is still trying to rollback it.

    In the end, we restarted the db server. After the db server is up, the session is no longer trying to rollback. The state of data is before the delete is executed.

    Can advise why after the restart, the db need not go through the pain rollback l? It's managed to rollback so fast?

    thanks

  • March 19, 2012 at 6:29 am

    #1461087

    The DB did go through the rollback after the restart. It's sometimes faster because there's no one using the DB, the flip side is that unless you're using enterprise edition, the DB is RECOVERING and unavailable during the rollback.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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