Transaction Rollback

  • 1. Lets say the transaction normally runs for 8 hrs, how long will the rollback take.

    2. If you terminate the rollback, what happens.

    3. If a query is running for over 8 hrs, will it timeout ?

    Regards

    Kevin

    sonyt65@yahoo.com

  • 1. Lets say the transaction normally runs for 8 hrs, how long will the rollback take.

    --It depends on the number of rows the transaction is dealing with,and not with the time taken.

    2. If you terminate the rollback, what happens.

    --When you begin a transaction,the impacted data is written to the cache and once you commit it it writes to the disk.If you rollback it instead of commiting it frees from the cache itself.If you terminate rollback--means the rollback command is not issued and so the data is still in the cache.

    3. If a query is running for over 8 hrs, will it timeout ?

    --Depends on your server settings.Default timeout period is 600 seconds.

  • clive (11/3/2008)


    1. Lets say the transaction normally runs for 8 hrs, how long will the rollback take.

    Probably at least 8 hours. Depends if those 8 hours were all doing processing or if the query was waiting for resources for a while. Also, if lots of that 8 hours was selects, then the rollback time will be less.

    General rule of thumb is that a rollback takes as long as the data modifications initially took.

    2. If you terminate the rollback, what happens.

    You can't terminate a rollback. Even if you restart the server, SQL will finish the rollback after the server starts. It's a requirement that data always be in a consistent state, which means a rollback can't be ignored.

    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 3 posts - 1 through 2 (of 2 total)

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