Online index rebuild or transactional offline DDL: which keeps schema-change rollback safer?
0 reputation · 28 Jun 2024, 15:32 UTC
I'm planning a schema migration on SQL Server (Enterprise Edition, recent version) that alters a large, heavily queried table. Two documented approaches are on the table: an online index rebuild with ONLINE = ON, which permits concurrent DML, or an offline change wrapped in BEGIN TRANSACTION so the DDL can roll back if anything fails mid-deployment.
The tension I can't resolve: the offline path acquires a Schema Modification (Sch-M) lock that blocks all table access, and the documented behavior is that rolling back a large change can take roughly as long as the original operation — so a failed migration could extend the outage rather than shorten it. The online path avoids most blocking but generates significant transaction log growth from row versioning, and I'm unsure how that interacts with log shipping and disk headroom during a long rebuild.
My concrete constraint is a short maintenance window on a multi-terabyte table where a failed deployment must not leave the application blocked for hours.
Given that constraint, which approach offers the more predictable worst-case recovery time? Does wrapping an online operation in an explicit transaction meaningfully change rollback behavior? And what should I monitor (e.g., sys.dm_tran_locks, log usage) to decide before committing in production?