Minimal logging

In one line: a bulk insert can write almost nothing to the transaction log, but only with a recovery model that allows it, a table lock, and a target with no nonclustered index. Miss any one and every row is logged.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/minimal-logging on GitHub.

What you’ll see

  • A nightly load that’s slow and makes the log huge, though it “uses TABLOCK“.
  • Log growth or big log backups after loads.
  • In the demo, the same 200,000 rows (43 MB) wrote anywhere from 0.22 MB to 81 MB of log, depending on how they were loaded.

Why it happens

Minimal logging records page allocations instead of rows, so a crash can still roll the load back. It needs all of these:

  • SIMPLE or BULK_LOGGED recovery. Under FULL nothing is minimal, because point-in-time restore depends on the log.
  • A table lock. A heap loaded without TABLOCK logged every row: 60.75 MB against 0.36 MB with it.
  • No nonclustered index on the target. One index made a TABLOCK load into a heap log row by row again: 61.12 MB.

Since SQL Server 2016, rows written to brand-new pages of a clustered index are minimally logged by default. An empty clustered table wrote 4.32 MB without TABLOCK and 0.22 MB with it.

What to do

  • Load with TABLOCK (or SELECT ... INTO).
  • Drop nonclustered indexes before a big load and rebuild them afterwards, or accept the log.
  • Under FULL recovery, still use TABLOCK. Those loads log whole page images: 44 MB in a few thousand records, against 61–81 MB row by row.
  • BULK_LOGGED keeps loads minimal while keeping a log chain. The cost: the next log backup copies the changed pages, and you can’t restore to a point inside it.

What doesn’t help

  • Testing in a database you just switched to FULL. Until its first full backup it behaves like SIMPLE, so the test measures the wrong thing.
  • TABLOCK on a table with nonclustered indexes. It’s row by row anyway.

Check your server

-- Recovery model per database, and whether FULL is really FULL
-- (no full backup yet means the log chain hasn't started, and it behaves like SIMPLE).
SELECT d.name,
       d.recovery_model_desc,
       LogChainStarted = CASE WHEN rs.last_log_backup_lsn IS NULL THEN 0 ELSE 1 END
FROM sys.databases AS d
JOIN sys.database_recovery_status AS rs ON rs.database_id = d.database_id
WHERE d.database_id > 4
ORDER BY d.name;

See it happen

The minimal-logging demo loads the same 200,000 rows seven ways under each recovery model, in real FULL with its log chain started. It measures the log each load’s own transaction wrote.

Related: why is my transaction log full? · backup and restore · columnstore and the delta store

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights