Transaction Log Records, VLFs, WAL Principles, Log Truncation, and Recovery

Learning outcomes

A transaction log file that grows is not automatically “too large,” and shrinking it is not the same as making log records reusable. SQL Server uses the log for atomicity, rollback, crash recovery, backups, replication/HA dependencies, and more. This lesson separates the logical log (an ordered sequence of log records identified by Log Sequence Numbers, or LSNs) from the physical log files divided into Virtual Log Files (VLFs).

1. WAL: log first, data page later

Write-ahead logging means the log records describing a data-page modification must be hardened before the corresponding dirty data page can be written to durable storage. Under normal fully durable commit semantics, the commit log record must also be hardened before the client is told that commit succeeded. This allows SQL Server to reconstruct consistency after a crash even though modified data pages can remain in memory for some time.

USE ServiceHubLab;GOSELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=DB_NAME();SELECT file_id,name,type_desc,size*8.0/1024 AS size_mb,growth,is_percent_growthFROM sys.database_files WHERE type_desc='LOG';SELECT total_log_size_in_bytes/1048576.0 AS total_log_mb,       used_log_space_in_bytes/1048576.0 AS used_log_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;GO

2. The physical log is divided into VLFs

SQL Server uses one or more physical log files, internally divided into VLFs. Administrators do not set VLF boundaries directly. Growth history determines their count and size. Current Microsoft documentation notes a SQL Server 2022+ change: when a growth event is at most 64 MB, the engine creates one VLF for that growth instead of the older four-VLF behavior. The larger point is operational: repeated tiny growth events can create excessive VLF counts, while a few huge VLFs can also be undesirable.

SELECT COUNT(*) AS vlf_count,       SUM(CASE WHEN vlf_active=1 THEN 1 ELSE 0 END) AS active_vlfs,       MIN(vlf_size_mb) AS min_vlf_mb,       MAX(vlf_size_mb) AS max_vlf_mbFROM sys.dm_db_log_info(DB_ID());SELECT TOP (20) file_id,vlf_begin_offset,vlf_size_mb,       vlf_sequence_number,vlf_active,vlf_status,vlf_first_lsnFROM sys.dm_db_log_info(DB_ID())ORDER BY file_id,vlf_begin_offset;GO

sys.dm_db_log_info is the supported replacement for old DBCC LOGINFO scripts. In SQL Server 2022+, it requires VIEW DATABASE PERFORMANCE STATE.

3. Reuse waits explain growth better than “the log is full”

Log truncation marks inactive VLF space reusable; it does not reduce the physical file size. What prevents truncation depends on recovery model and workload state: an active transaction, missing log backup under FULL/BULK_LOGGED, availability/replication dependencies, checkpoint state, and other reasons can all hold the active log. The first diagnostic question is therefore log_reuse_wait_desc, not “how often should we shrink?”

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab08') IS NULL EXEC(N'CREATE SCHEMA lab08 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab08.LogProbe;CREATE TABLE lab08.LogProbe(id int NOT NULL PRIMARY KEY,payload varchar(2000) NOT NULL);BEGIN TRANSACTION;;WITH n AS( SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab08.LogProbe(id,payload)SELECT n,REPLICATE('L',1800) FROM n;SELECT @@TRANCOUNT AS session_a_trancount;-- Leave this transaction open while Session B inspects log evidence.GO

USE ServiceHubLab;GOSELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=DB_NAME();SELECT total_log_size_in_bytes/1048576.0 AS total_log_mb,       used_log_space_in_bytes/1048576.0 AS used_log_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;DBCC OPENTRAN WITH NO_INFOMSGS;GO

The expected evidence is an open transaction and commonly ACTIVE_TRANSACTION as a reuse wait while Session A remains open, though timing/state can vary. Return to Session A and ROLLBACK, then run CHECKPOINT in the SIMPLE-recovery lab and recheck. Do not infer that a checkpoint is a substitute for log backups in FULL recovery.

ROLLBACK TRANSACTION;GO-- Session B or a fresh session:CHECKPOINT;SELECT name,log_reuse_wait_descFROM sys.databases WHERE name=DB_NAME();SELECT used_log_space_in_percentFROM sys.dm_db_log_space_usage;GO

4. Truncate, shrink, and recover are different operations

Truncation changes which VLFs can be reused in the circular logical log. DBCC SHRINKFILE attempts to reduce a physical file and can only remove free space at the end after VLF layout permits it. Routine shrinking followed by autogrowth creates churn and can recreate poor VLF layout. Size the log for normal peaks, use sensible fixed growth increments, and investigate why reuse is delayed.

A checkpoint writes dirty pages for a database and records a recovery point. Traditional crash recovery is often described as analysis (determine state/work), redo (reapply necessary logged changes), and undo (reverse uncommitted work). Accelerated Database Recovery changes the internal work and can make undo much faster, but the same three-phase conceptual structure remains.

5. Production judgment and cleanup

The ServiceHub starter lab intentionally uses SIMPLE recovery; that is a learning baseline, not a production recommendation. Chapter 15 will design backup/recovery models from recovery point objectives. For now, record recovery model, log size, growth increment, VLF count, reuse wait, longest transaction, and HA/replication dependencies before changing anything. VLF remediation can involve a one-time controlled shrink/regrow sequence, but only after valid backups and a documented maintenance plan—not as routine maintenance.

DROP TABLE IF EXISTS lab08.LogProbe;GO

Authoritative references

• Transaction log architecture and management guide — WAL, LSNs, VLFs and circular log
• sys.dm_db_log_info — VLF evidence
• Troubleshoot full transaction log — reuse waits
• Database checkpoints — dirty-page persistence and recovery points
• SQL Server 2025 build versions — servicing baseline