Query to know time of rollback.

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  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

• Adam Bean

  One Orange Chip

  Points: 26631

  More actions

  June 5, 2009 at 11:08 am

  #1005438

  KILL XX WITH STATUSONLY

  XX = spid

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  June 8, 2009 at 5:07 am

  #1005996

  Thanks.

  Regards,

  JMSM

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• GURSETHI

  SSCommitted

  Points: 1912

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• JMSM

  SSCertifiable

  Points: 5440

  More actions

  June 8, 2009 at 12:16 pm

  #1006236

  Thanks a lot everybody.

  Regards,

  JMSM