SQL Server – Cancelling or Stopping ALTER INDEX REORGANIZE

fragmentationindexsql server

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 ?

Best Answer

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.