Post reply

Query to know time of rollback.

  • June 5, 2009 at 10:23 am

    #155788

    Hello,

    Does any one can send me a query to know what time should be the time of one rollback statement?

    We've got a process that is running since 2 pm and Dev team is asking us to know how much time will be the rollback if we kill the process that is running.

    Thanks and regards,

    JMSM 😉

  • June 5, 2009 at 11:08 am

    #1005438

    KILL XX WITH STATUSONLY

    XX = spid

  • June 8, 2009 at 5:07 am

    #1005996

    Thanks.

    Regards,

    JMSM 😉

  • June 8, 2009 at 5:57 am

    #1006018

    General rule-of-thumb. It will take longer to roll a process back than it did to do the work to that point. So if you roll back a process that has been running for 4 hours, rough estimate is that the rollback will take minimum 4 hours.

    This, of course, assumes that the whole 4 hours has been spent working in one transaction. If a significant time was spent waiting for locks, or there were multiple transactions in that 4 hours, then the rollback will take less time.

    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

  • June 8, 2009 at 6:12 am

    #1006029

    Hi,

    We can also query a DMV which will tell how much %age a specific query has done.

    SELECT SESSION_ID, percent_complete, estimated_completion_time FROM SYS.DM_EXEC_REUQESTS

    Regards

    GURSETHI

  • June 8, 2009 at 10:10 am

    #1006151

    GURSETHI (6/8/2009)

    Hi,

    We can also query a DMV which will tell how much %age a specific query has done.

    SELECT SESSION_ID, percent_complete, estimated_completion_time FROM SYS.DM_EXEC_REUQESTS

    From Books Online:

    percent_complete

    Percent of work completed for certain operations, including rollbacks.

    Note:

    This does not provide progress data for queries.

    So it will give you info on a rollback in progress, but it will not help with the percentage complete of a generic query. I believe backups and restores populate this, perhaps CheckDB. Not much else does.

    Also from Books Online

    estimated_completion_time

    Internal only.

    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

  • June 8, 2009 at 12:16 pm

    #1006236

    Thanks a lot everybody.

    Regards,

    JMSM 😉

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

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