Online ALTER COLUMN final Sch-M lock duration bounds under concurrent DML in SQL Server 2019

Answer

There is no published upper bound for the final schema‑modification (Sch‑M) lock taken during an online ALTER COLUMN in SQL Server 2019. In practice the lock is held only for the time needed to flush the log and acquire the metadata lock – usually a few milliseconds – but it can extend to several seconds when the transaction log is being flushed, a checkpoint is in progress, or any session holds a shared lock on the table for a long time.

Confirmed facts

• SQL Server documentation does not specify a maximum duration for the final Sch‑M lock.
• The lock can be indirectly bounded by setting a session‑level LOCK_TIMEOUT; if the wait exceeds the timeout the statement aborts with error 1222.
• No DMV shows real‑time progress of the data‑copy phase, so you cannot predict when the Sch‑M lock will be taken.

Likely explanation

The final Sch‑M lock is taken after the online data‑copy phase completes. Its duration depends on:

1. Log flush activity – the operation must wait for the log to be hardened before it can acquire the Sch‑M lock.
2. Checkpoint or lazy writer activity that may delay log hardening.
3. Any existing shared (S) or update (U) locks held by long‑running transactions that block the Sch‑M lock.

Under heavy write load these factors can cause the wait to stretch from milliseconds to seconds.

Bounding or aborting the lock

You can bound the wait by issuing SET LOCK_TIMEOUT 5000; (or any value in milliseconds) in the session that runs the ALTER COLUMN. If the lock cannot be acquired within the timeout, the statement is cancelled and error 1222 is returned. This does not require third‑party tools. However, aborting an online ALTER COLUMN after it has started the data‑copy phase may leave the table in a partially altered state; you must be prepared to roll back or restore from a backup.

Work‑arounds for zero‑downtime when the Sch‑M wait is unacceptable

• Add a nullable column of the target type, populate it in small batches using UPDATE with a WHERE clause that limits rows, switch application logic to read/write the new column, then drop the old column.
• If the table is partitioned, switch an empty partition with the new definition and merge.
• Schedule the ALTER COLUMN during a maintenance window with no concurrent DML, or use a secondary replica (Always On/Log Shipping) to apply the change and then failover.
• Use replication or change‑data‑capture to keep a copy in sync while the primary is altered, then swap.

Verification steps (safe, scoped)

1. Check for blocking shared locks before running the change:

   SELECT request_session_id, resource_type, request_mode
   FROM sys.dm_tran_locks
   WHERE resource_associated_entity_id = OBJECT_ID('dbo.YourTable')
     AND request_mode IN ('S','U');
   

2. Set a short timeout and test:

   SET LOCK_TIMEOUT 2000; -- 2 seconds
   ALTER TABLE dbo.YourTable ALTER COLUMN ColName BIGINT;
   

   If the statement fails with Msg 1222, the lock wait exceeded the timeout.
3. Monitor log flush during the data‑copy phase (optional):

   SELECT *
   FROM sys.dm_db_log_stats(DB_ID(), OBJECT_ID('dbo.YourTable'));
   

   High log I/O correlates with longer Sch‑M waits.

If you observe long‑running shared locks, consider resolving them (e.g., by completing or killing the blocking sessions) before attempting the ALTER COLUMN with a bounded LOCK_TIMEOUT.