0

I tried an alter table X add column C, but it was taking too long and rolled back(actually killed it). When I did so, sp_who2 showed DiskIO = 5 mil.

Is it meaningful to assume that it will take another 5 mil to complete the rollback, regardless of time? If not, is there some kind of multiplier that has to do with the transaction logs?

1 Answer 1

2

Is it meaningful to assume that it will take another 5 mil to complete the rollback, regardless of time? If not, is there some kind of multiplier that has to do with the transaction logs?

Whatever was done needs to be undone. There is almost never an exact 1 to 1 correlation and quite generally rollback takes a much longer time. This all depends on various factors that admins can't generally control.

3
  • 3
    Admins may want to use Accelerated Database Recovery to avoid these unfortunate circumstances. Commented Sep 26, 2023 at 13:41
  • I was sure the time would be multiplied, but I though maybe the IO would would be. It actually got pretty close: The rollback took 6 mil diskIO. These 6 took 5 hours in contrast to the 1.25 hours the initial 5M IO took. Commented Sep 26, 2023 at 19:45
  • ADR/CTR may help in certain situations, however it does not cover all items and non-ADR possible transactions may occur. Commented Sep 26, 2023 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.