Query to know time of rollback.

Question

Post reply

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 😉

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Adam Bean

One Orange Chip

Points: 26631

June 5, 2009 at 11:08 am

#1005438

KILL XX WITH STATUSONLY

XX = spid

JMSM SSCertifiable Points: 5440 More actions · Answer 2

JMSM

SSCertifiable

Points: 5440

June 8, 2009 at 5:07 am

#1005996

Thanks.

Regards,

JMSM 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 · Answer 4

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 · Answer 5

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.