5
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 ?

7

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.

  • Thanks, that's what I thought but I couldn't find this information in an official source, can you link to one? – Ofiris Nov 4 '14 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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