Question
ALTER TABLE SWITCH rollback behavior with active referencing transactions
Rin Finch
0 reputation · 03 Mar 2022, 16:08 UTC
54.6K views1
Loading…
0 reputation · 03 Mar 2022, 16:08 UTC
Transactional DDL in SQL Server generally allows most CREATE, ALTER and DROP statements to be rolled back inside an explicit user transaction. Partition switching via ALTER TABLE ... SWITCH is a metadata operation intended to move data between partitions with minimal logging.
The design question is whether the switch remains fully rollback safe when active transactions exist that reference the source or target partition. Documentation describes the operation as metadata-only and subject to schema stability locks, but does not clarify the outcome of a ROLLBACK after the switch has been applied, or how concurrent referencing transactions are handled.
Does a ROLLBACK revert the partition mapping after a successful SWITCH? Under what conditions does SQL Server block or allow the SWITCH when active transactions reference the partitions? Is the operation treated as fully transactional for rollback purposes in current supported versions?
34775 reputation · 04 Mar 2022, 02:44 UTC
Yes, ALTER TABLE ... SWITCH is fully rollback-safe inside an explicit user transaction. A ROLLBACK after a successful SWITCH reverts the partition metadata to its pre-switch state. The operation acquires schema modification (Sch-M) locks on both source and target tables, so any active transaction holding incompatible locks (Sch-S, S, U, X) on either table will block the SWITCH until those transactions commit or roll back. In autocommit mode (no explicit BEGIN TRAN), the SWITCH commits immediately and cannot be rolled back.
microsoftsqlserverBEGIN TRAN;ALTER TABLE Source SWITCH PARTITION n TO Target;SELECT $PARTITION.pf(col), COUNT(*) FROM Source GROUP BY $PARTITION.pf(col); (partition n should show 0 rows).ROLLBACK;Are you running the SWITCH inside an explicit BEGIN TRAN / COMMIT / ROLLBACK block, or in autocommit mode? If autocommit, the operation is not rollback-safe. If explicit, the behavior above applies.
Use comments to ask for clarification. Post a solution as an answer.
6,510 reputation · 03 Mar 2022, 21:52 UTC
To clarify the interaction between ALTER TABLE ... SWITCH and concurrent transactions, it is important to note that this operation requires a Schema Modification (Sch-M) lock on both the source and target tables. Because Sch-M locks are incompatible with all other lock modes—including the Schema Stability (Sch-S) locks held by active readers—the operation cannot execute while other transactions are referencing the partitions.
ROLLBACK reverts the metadata mapping to its original state.SWITCH will block until all active referencing transactions commit or roll back. Conversely, once the SWITCH begins, it blocks all new access to both tables until the transaction completes.READ_COMMITTED_SNAPSHOT) does not bypass this; Sch-M locks still enforce strict serialization.For verification in a test environment, monitoring sys.dm_tran_locks during the operation will confirm the Sch-M acquisition and the resulting blocking of concurrent sessions.