7 
ALTER INDEX [myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON )

I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it has over 10 Billion rows. (About 1 TB of data including myIndex (non-clustered)).

I know there is progress as when I query for fragmentation, I see the number decrease.

If I cancel / stop the execution of the ALTER INDEX, can I resume it later safely, or will it cause to a rollback?

Will it rollback roll just the last page swapped or the whole operation ?

Improve this question
1
  • You can also see the progress in sys.dm_exec_requests.percent_complete.
    – Der_Meister
    Dec 27, 2021 at 23:19

1 Answer 1

Reset to default
9

Yes, you can stop a reorganize and it won't cause a big rollback like you are talking about. You will be left with where the operation left off (that's a good thing). It's a rebuild that would have the rollback behavior.

Improve this answer
2

Your Answer

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

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