ALTER COLUMN on Large Tables: Avoiding Transaction Log Explosion

Why ALTER COLUMN Is Not "Just a Metadata Change"

Most DBAs expect DDL to be fast, because most DDL is fast: adding a column with a default, creating a check constraint WITH NOCHECK, renaming a table. ALTER COLUMN breaks that expectation for one simple reason: in many cases it is a size-of-data operation. SQL Server has to touch every row to verify or rewrite it, and every row it touches gets logged, inside a single, uninterruptible transaction.

-- 500 million rows, changing a VARCHAR from 20 to 15 characters
ALTER TABLE dbo.CustomerOrders
ALTER COLUMN OrderRef VARCHAR(15) NOT NULL;

-- ✗ Full table scan to validate every value fits and is non-NULL
-- ✗ Every row rewritten and logged in ONE transaction
-- ✗ Table is SCH-M locked for the entire duration: no reads, no writes
-- ✗ Transaction log grows to roughly the size of the table (or more)
-- ✗ If the log runs out of space, the operation fails and rolls back
--   the ENTIRE rewrite, which can take longer than the ALTER itself

When It's Cheap vs. When It's Expensive

Not every ALTER COLUMN is a disaster. SQL Server (2016+) optimizes a specific case: increasing the length of a variable-length type (VARCHAR, NVARCHAR, VARBINARY) with no other change. That's metadata-only: no rewrite, no meaningful log activity.

Strategy 1: Batch Rebuild via Shadow Column

The Concept

Instead of asking SQL Server to rewrite 500 million rows in one transaction, do it yourself in small, committed batches. Add the new column, populate it in chunks, then swap it in.

-- 1. Add the new column (fast if nullable, no default)
ALTER TABLE dbo.CustomerOrders
ADD OrderRef_New VARCHAR(15) NULL;

-- 2. Populate in batches, committing after each one
DECLARE @BatchSize INT = 20000;
DECLARE @RowsUpdated INT = 1;

WHILE @RowsUpdated > 0
BEGIN
  UPDATE TOP (@BatchSize) dbo.CustomerOrders
  SET OrderRef_New = LEFT(OrderRef, 15)
  WHERE OrderRef_New IS NULL;

  SET @RowsUpdated = @@ROWCOUNT;
  -- COMMIT here if wrapped in an explicit transaction per batch
  WAITFOR DELAY '00:00:00.250';  -- let other sessions in between batches
END;

-- 3. Swap columns (fast, metadata operations)
ALTER TABLE dbo.CustomerOrders DROP COLUMN OrderRef;
EXEC sp_rename 'dbo.CustomerOrders.OrderRef_New', 'OrderRef', 'COLUMN';
ALTER TABLE dbo.CustomerOrders ALTER COLUMN OrderRef VARCHAR(15) NOT NULL;
-- (this final ALTER still scans, but only to enforce NOT NULL:
--  much cheaper once the data is already correctly shaped)

Strategy 2: Manage the Log Instead of Fighting It

Recovery Model and Log Backups

Batching only helps the log if something actually truncates it between batches. Under the FULL recovery model, that means log backups: not just checkpoints.

-- During a long batch run, keep log backups frequent
BACKUP LOG [YourDatabase] TO DISK = N'\\backupshare\YourDatabase_log.trn';

-- Schedule this every 1-2 minutes for the duration of the batch job,
-- or trigger it from the same script after every N batches.
-- Without this, FULL recovery still accumulates log records
-- across "committed" batches; they're just individually small.

Under SIMPLE recovery, log space is reclaimed automatically after each checkpoint following a committed batch; no manual log backup needed, but you lose point-in-time recovery for the duration.

Pre-Size the Log: Don't Let Autogrowth Do It

Every autogrowth event stalls the operation while the file extends, and default percentage-based growth produces hundreds of small VLFs, which slows crash recovery and log backups for a long time afterward.

-- Grow the log to a realistic target BEFORE starting the batch job
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = YourDatabase_log, SIZE = 20GB);

-- Set fixed-size autogrowth as a safety net, not the primary mechanism
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = YourDatabase_log, FILEGROWTH = 1024MB);

-- Check current VLF count and log usage
DBCC LOGINFO;
SELECT total_log_size_in_bytes, used_log_space_in_bytes
FROM sys.dm_db_log_space_usage;

Strategy 3: Rebuild via New Table (for the biggest tables)

The Concept

For tables in the hundreds of millions of rows, or when a maintenance window is available, it's often faster and safer to build a new table with the target schema, bulk-copy the data, and swap it in, rather than altering in place.

-- 1. Create the target table with the correct schema up front
CREATE TABLE dbo.CustomerOrders_New (
  OrderID       BIGINT       NOT NULL,
  OrderRef      VARCHAR(15)  NOT NULL,
  CustomerID    INT          NOT NULL,
  OrderDate     DATETIME2    NOT NULL
);

-- 2. Bulk copy in batches: minimally logged if recovery model
--    is SIMPLE/BULK_LOGGED and the target has no indexes yet
INSERT INTO dbo.CustomerOrders_New WITH (TABLOCK)
SELECT OrderID, LEFT(OrderRef, 15), CustomerID, OrderDate
FROM dbo.CustomerOrders
WHERE OrderID BETWEEN 1 AND 10000000;
-- repeat in ID ranges, or drive with a loop / SSIS / batch script

-- 3. Build indexes and constraints on the new table

-- 4. Swap (requires a short exclusive lock, but it's fast)
EXEC sp_rename 'dbo.CustomerOrders', 'CustomerOrders_Old';
EXEC sp_rename 'dbo.CustomerOrders_New', 'CustomerOrders';

If the table is partitioned, ALTER TABLE ... SWITCH PARTITION is even better: it's a pure metadata operation with no data movement or logging of row-level changes at all; build the reshaped data in a staging table per partition, then switch.

Comparison of Approaches

Monitoring While It Runs

-- Watch log usage live during the operation
SELECT
  name,
  size / 128.0 AS size_mb,
  FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS used_mb
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Watch for lock escalation and blocking during batches
SELECT
  request_session_id,
  resource_type,
  request_mode,
  request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('YourDatabase')
ORDER BY request_session_id;

Best Practices Checklist

• ☐ Check whether the change is actually metadata-only (increasing VARCHAR/NVARCHAR/VARBINARY length) before doing anything else
• ☐ Drop non-essential indexes on the column before altering, rebuild after
• ☐ Never run a direct ALTER COLUMN against a table with tens of millions of rows without testing first
• ☐ Use a shadow column + batched UPDATE for zero-downtime changes on live production tables
• ☐ Under FULL recovery, schedule frequent log backups for the duration of the batch run, not just at the usual interval
• ☐ Pre-grow the log to a realistic size in fixed MB increments; don't rely on autogrowth mid-operation
• ☐ For the largest tables, consider a new-table-and-swap rebuild during a maintenance window instead
• ☐ For partitioned tables, prefer SWITCH PARTITION over any in-place ALTER
• ☐ Test the exact operation against a restored copy first and measure log growth before touching production

The Bottom Line

ALTER COLUMN looks like a one-line DDL statement, but on a large table it behaves like a full-table UPDATE wrapped in a single transaction, with all the log growth and locking that implies. Test whether your specific change is metadata-only first. If it isn't, don't run it directly against a huge table: break the rewrite into small, committed batches with a shadow column, keep the log reclaimable as you go, and reserve the new-table-and-swap approach for the tables where even batching is too slow.