SQL Server Transaction Log Internals: WAL, VLFs and Checkpoints (2026)

Write-Ahead Logging: The Core Guarantee

SQL Server uses Write-Ahead Logging (WAL): before any data page modification is written to disk, the corresponding log records describing that modification must be written to the log file first. This invariant is what makes crash recovery possible.

The flow of a single UPDATE statement:

1. SQL Server reads the target data page into the buffer pool (if not already there)
2. It writes log records describing the change (before-image, after-image, transaction ID, LSN) to the log buffer in memory
3. It modifies the data page in the buffer pool (the page is now "dirty")
4. On COMMIT, the log buffer is flushed to the .ldf file — this is the WRITELOG wait
5. Once the log flush completes, COMMIT returns to the caller
6. The dirty data page is written to the .mdf/.ndf files later — lazily, by the checkpoint process

Log Records and Log Sequence Numbers

Every change to the database produces one or more log records. Each log record contains:

• LSN (Log Sequence Number) — a globally unique, monotonically increasing identifier: VLF sequence : log block : record within block
• Transaction ID — which transaction produced this record
• Operation type — INSERT, UPDATE, DELETE, page allocation, index split, etc.
• Before image — the original value (for rollback)
• After image — the new value (for redo after a crash)
• Previous LSN for this transaction — forms a backward-linked chain per transaction for fast rollback

-- Read the active portion of the transaction log (use sparingly)
SELECT TOP 100
    [Current LSN],
    Operation,
    Context,
    [Transaction ID],
    [Log Record Length],
    AllocUnitName,
    [Begin Time]
FROM fn_dblog(NULL, NULL)
ORDER BY [Current LSN];

Virtual Log Files (VLFs)

A physical log file (.ldf) is divided internally into Virtual Log Files (VLFs). VLFs are the unit of log truncation — SQL Server cannot reuse space within a VLF until the entire VLF is no longer needed (all transactions in it are committed and checkpointed, or backed up in Full recovery).

SQL Server creates VLFs automatically when the log file is first created or grows. The number of VLFs created per growth event depends on the growth size:

-- Check VLF count and status
DBCC LOGINFO;
-- OR (SQL Server 2016+):
SELECT database_id, vlf_sequence_number, vlf_active, vlf_status,
       vlf_size_mb, vlf_begin_lsn, vlf_create_lsn
FROM sys.dm_db_log_info(DB_ID())
ORDER BY vlf_sequence_number;

vlf_status: 0 = inactive (reusable), 2 = active (contains needed log records).

VLF Fragmentation and Performance Impact

A log file that has grown many times via small autogrowth events can accumulate thousands of VLFs. This causes:

• Slow database startup — SQL Server scans all VLFs during crash recovery and log initialization
• Slow log backup and restore — backup must read and catalogue every VLF header
• Slow log truncation — the engine checks each VLF status during truncation

-- Count VLFs per database (> 50 is concerning; > 1000 is a problem)
SELECT DB_NAME(database_id) AS db_name, COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(NULL)  -- NULL = all databases
GROUP BY database_id
ORDER BY vlf_count DESC;

To fix VLF fragmentation, shrink the log then pre-size it with a single large growth event:

USE YourDB;

-- Step 1: Back up the log to free inactive VLFs
BACKUP LOG YourDB TO DISK = 'NUL';  -- dev/test only; use real backup in production

-- Step 2: Shrink the log file to minimum (removes empty VLFs)
DBCC SHRINKFILE (YourDB_log, 1);

-- Step 3: Pre-size to desired final size in ONE growth event
-- (creates 16 VLFs for a 10 GB file)
ALTER DATABASE YourDB
MODIFY FILE (NAME = YourDB_log, SIZE = 10240MB, FILEGROWTH = 1024MB);

The Checkpoint Process

SQL Server's checkpoint process writes all dirty data pages from the buffer pool to the data files, then writes a checkpoint record to the log. After a checkpoint, SQL Server knows that all changes up to that LSN are safely on disk in the data files — so on crash recovery, REDO only needs to start from the oldest active transaction, not the beginning of the log.

Checkpoint types:

• Automatic checkpoint — triggered when the estimated crash-recovery time would exceed the recovery interval setting (default: 0 = ~1 minute)
• Indirect checkpoint (SQL 2012+, default for new databases) — targets a fixed recovery time (TARGET_RECOVERY_TIME per database), flushing dirty pages more smoothly
• Manual checkpoint — CHECKPOINT [checkpoint_duration]
• Internal checkpoint — triggered by log backup, database snapshot, or ALTER DATABASE

-- Check checkpoint settings
SELECT name, target_recovery_time_in_seconds, recovery_model_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Enable indirect checkpoint (recommended; smooths I/O)
ALTER DATABASE YourDB SET TARGET_RECOVERY_TIME = 60 SECONDS;

Log Truncation vs Log Shrink

Log truncation marks inactive VLFs as reusable. Space is not returned to the OS — it stays within the .ldf file for SQL Server to reuse. Truncation happens automatically after:

• A checkpoint (in Simple recovery model)
• A log backup (in Full or Bulk-Logged recovery model)

Log shrink returns physical file space to the OS. It should be done rarely and only after confirming the log is genuinely oversized:

-- Shrink after truncation (after log backup or checkpoint)
DBCC SHRINKFILE (YourDB_log, 512);  -- target 512 MB

log_reuse_wait_desc: Why the Log Won't Truncate

When the log cannot be truncated, sys.databases.log_reuse_wait_desc tells you why:

SELECT name, log_reuse_wait_desc, recovery_model_desc,
       log_size_mb = (SELECT SUM(size)*8/1024 FROM sys.master_files
                      WHERE database_id = d.database_id AND type=1)
FROM sys.databases d
WHERE name = DB_NAME();

-- Find the oldest active transaction blocking log truncation
SELECT
    s.session_id,
    s.login_name,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS minutes_open,
    at.transaction_type,
    at.transaction_state,
    t.text AS last_sql
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t
ORDER BY at.transaction_begin_time;

Recovery Models: Simple, Full, Bulk-Logged

-- Check and change recovery model
SELECT name, recovery_model_desc FROM sys.databases WHERE name = DB_NAME();
ALTER DATABASE YourDB SET RECOVERY FULL;
ALTER DATABASE YourDB SET RECOVERY SIMPLE;  -- dev/test only

Log Backups in Full Recovery

In Full recovery, the log grows until a log backup is taken. Frequency determines both your RPO (recovery point objective) and maximum log size:

-- Manual log backup
BACKUP LOG YourDB
TO DISK = 'D:\Backups\YourDB_log_20260611_1400.bak'
WITH COMPRESSION, STATS = 10;

-- Check backup history and log backup frequency
SELECT
    bs.database_name,
    bs.type,                      -- 'L' = log
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size / 1048576.0   AS backup_mb
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'YourDB'
  AND bs.type = 'L'
ORDER BY bs.backup_start_date DESC;

For OLTP databases, take log backups every 15–30 minutes. For high-transaction databases (hundreds of MB/hour of log), every 5 minutes may be necessary to control log size.

Log File Sizing and Pre-Growth Strategy

The ideal log size = peak log generation rate × longest interval between log backups × 1.5 safety margin.

-- Measure log generation rate (run twice, 60 seconds apart, subtract)
SELECT log_bytes_used = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Bytes Flushed/sec'
  AND instance_name = 'YourDB';

-- Or check log usage trend from backup history
SELECT
    backup_start_date,
    backup_size / 1048576.0  AS log_backup_mb,
    DATEDIFF(MINUTE, LAG(backup_start_date) OVER (ORDER BY backup_start_date),
             backup_start_date) AS minutes_since_last
FROM msdb.dbo.backupset
WHERE database_name = 'YourDB' AND type = 'L'
ORDER BY backup_start_date DESC;

-- Pre-size strategy: one growth event to final size, small autogrowth as safety
ALTER DATABASE YourDB
MODIFY FILE (
    NAME = YourDB_log,
    SIZE = 10240MB,        -- pre-sized to 10 GB
    FILEGROWTH = 1024MB    -- 1 GB emergency autogrowth only
);

Monitoring Log Usage

-- Current log usage percentage
SELECT
    DB_NAME(database_id) AS db_name,
    log_size_mb = cntr_value / 1024.0
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Size (KB)';

-- More useful: used vs total
SELECT
    DB_NAME()                                       AS db_name,
    total_log_size_mb   = SUM(total_log_size_in_bytes) / 1048576.0,
    used_log_space_mb   = SUM(used_log_space_in_bytes) / 1048576.0,
    used_pct            = 100.0 * SUM(used_log_space_in_bytes)
                              / NULLIF(SUM(total_log_size_in_bytes), 0)
FROM sys.dm_db_log_space_usage;

-- Alert if log > 80% full
SELECT DB_NAME(vfs.database_id) AS db_name,
       mf.name,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs
JOIN sys.master_files mf ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id
WHERE mf.type = 1  -- log files
ORDER BY avg_write_ms DESC;

Read Next

• Storage Engine: Data Pages, Extents and Heaps
• Locking: Lock Types, Escalation and RCSI
• Isolation Levels: READ UNCOMMITTED to Snapshot
• Blocking and Deadlocks: Detection and Resolution
• All SQL Server Articles