SQL Server log autogrow stall: why every write pauses while the log file grows

Applications report periodic, mysterious write stalls. Throughput drops briefly, then recovers. Repeat. The transaction log on the affected database is creeping upward, and there are no alarms on disk space. What you are seeing is almost certainly log autogrow stall: every write transaction in the database pauses while SQL Server expands and zero-initializes the transaction log file.

Log autogrow is one of the most expensive routine operations SQL Server performs against its own files. Unlike data files, the transaction log cannot use Instant File Initialization (IFI). Every byte of new log space must be written with zeros before the engine accepts new log records. While that zeroing happens, every transaction that needs to log a modification waits. The wider and slower your storage, the longer the stall.

This guide covers the mechanism, detection from the default trace and error log, how to distinguish the pattern from other write-latency problems, and how to size and configure the log file so the engine never autogrows during normal operations.

What this means

When SQL Server decides the transaction log file needs to grow, it issues a single serialized operation that pauses all I/O to that file. The expansion is not concurrent with normal work; it gates normal work. While the new extent is allocated and zeroed, every worker thread that needs to log a modification waits. From the application’s perspective, every write query hangs at the same instant.

The reason log growth is so much more expensive than data file growth is IFI. SQL Server can use IFI to skip zero-initialization for data files when the service account holds the SE_MANAGE_VOLUME_NAME privilege. IFI does not apply to log files. Every growth event writes zeros across the entire new allocation. On direct-attached NVMe this may be fast enough to ignore. On cloud-managed disks with IOPS caps, on shared SAN storage, or on network-attached volumes, the zero-initialization can take seconds. On a percentage-based autogrow for a multi-hundred-GB log, it can take tens of seconds and eventually time out.

SQL Server 2022 (16.x) introduced a narrow exception: transaction log autogrowth events up to 64 MB can benefit from IFI. This is helpful but is not a license to keep autogrow on as a sizing strategy. For a multi-TB database, chasing the 64 MB optimization by triggering hundreds of small growth events creates thousands of Virtual Log Files (VLFs), which degrades crash recovery, backup, and log-reader performance. The correct posture is to pre-size the log and reserve autogrow as an emergency pressure relief valve, not as the primary growth mechanism.

flowchart TD
    A[Log file near full] --> B[Autogrow triggered]
    B --> C[SQL Server pauses all log writes]
    C --> D[Zero-initialize new extent]
    D --> E{Completed in time?}
    E -- Yes --> F[Msg 5145 logged
writes resume]
    E -- No --> G[Msg 5144: autogrow cancelled]
    G --> H{Retry succeeds?}
    H -- Yes --> F
    H -- No --> I[Error 9002
writes fail]

Common causes

Quick checks

-- 1. Log space and reuse wait per database
SELECT
    db.name,
    ls.cntr_value AS log_used_pct,
    db.log_reuse_wait_desc,
    db.recovery_model_desc
FROM sys.dm_os_performance_counters ls
JOIN sys.databases db ON ls.instance_name = db.name
WHERE ls.counter_name = 'Percent Log Used'
  AND ls.object_name LIKE '%Databases%';

-- 2. Log file sizing and growth configuration
SELECT
    DB_NAME(database_id) AS db_name,
    name AS logical_file_name,
    size * 8 / 1024 AS current_size_mb,
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE type_desc = 'LOG'
ORDER BY DB_NAME(database_id);

-- 3. Recent autogrow events from the default trace
SELECT
    te.name AS event_name,
    t.DatabaseName,
    t.FileName,
    t.Duration / 1000 AS duration_ms,
    t.StartTime,
    t.IntegerData * 8 / 1024 AS growth_mb
FROM sys.fn_trace_gettable(
    (SELECT [path] FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE te.name IN ('Data File Auto Grow', 'Log File Auto Grow')
ORDER BY t.StartTime DESC;

-- 4. VLF count for one database
SELECT DB_NAME(database_id) AS db_name, COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'))
GROUP BY database_id;

-- 5. Autogrow timeouts or slow growth in the current error log
EXEC sp_readerrorlog 0, 1, 'Autogrow';

-- 6. Engine version (16 = SQL Server 2022, qualifies for 64 MB log IFI)
SELECT
    SERVERPROPERTY('ProductVersion') AS version,
    SERVERPROPERTY('ProductMajorVersion') AS major_version;

-- 7. Log file I/O stall
SELECT
    DB_NAME(vfs.database_id) AS db_name,
    mf.name AS file_name,
    vfs.io_stall_write_ms,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
         ELSE 0 END AS avg_write_latency_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_desc = 'LOG'
ORDER BY vfs.io_stall_write_ms DESC;

How to diagnose it

1. Confirm the symptom. Check the default trace for recent Log File Auto Grow events (EventClass 93). If you see events during the window when users reported stalls, you have your culprit. The Duration column is in microseconds; divide by 1000 for milliseconds.

2. Read the error log for autogrow timeouts. Search for “Autogrow” or the message IDs 5144 and 5145. Msg 5144 indicates an autogrow that was cancelled by the user or timed out; this is the smoking gun for a growth event the engine could not complete in time. Msg 5145 indicates an autogrow that completed but took longer than SQL Server considers healthy.

3. Inspect the growth configuration. is_percent_growth = 1 is almost always wrong for a transaction log. Percentage growth on a large log is catastrophic: a 10% growth on a 345 GB log triggers a 34.5 GB zero-initialization event that will time out and retry in a loop.

4. Examine log_reuse_wait_desc. If the log is growing because of LOG_BACKUP, fix the backup chain. If ACTIVE_TRANSACTION, find and address the open transaction. If REPLICATION or AVAILABILITY_REPLICA, the secondary or the replication agent is not consuming log fast enough. Adding log space does not fix any of these; it only delays the next autogrow.

5. Check VLF count. sys.dm_db_log_info(DB_ID()) returns one row per VLF. Hundreds of small autogrow events produce thousands of VLFs. Above 1000 you should plan a log rebuild. Above 10000 you will see it in crash recovery time.

6. Correlate with wait statistics. PREEMPTIVE_OS_WRITEFILEGATHER is the wait type SQL Server records while zero-initializing a file. A spike in this wait during the stall window confirms the mechanism. WRITELOG may also be elevated, but it represents normal log flush latency, not autogrowth specifically.

7. Verify whether the SQL Server 2022+ log IFI optimization applies. SERVERPROPERTY('ProductMajorVersion') returns 16 for SQL Server 2022. If the major version is 16 or higher and your autogrowth increment is 64 MB or less, the zero-initialization is partially mitigated. If the growth increment is larger than 64 MB, the optimization does not apply.

Metrics and signals to monitor

Fixes

Pre-size the log file

Pre-sizing is the only correct long-term answer. Estimate the log space needed to span your longest expected interval between log backups (typically 15 to 60 minutes for full recovery) plus headroom for batch operations and index rebuilds. Grow the file once, deliberately, during a maintenance window when the storage subsystem can absorb the zero-initialization without impacting user workloads.

-- Grow the log file to a fixed size
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = [YourDatabase_Log], SIZE = 50GB);

Set a sane fixed growth increment

If you must keep autogrow enabled as a safety net, set a fixed MB increment rather than a percentage. Choose a size large enough that the engine does not autogrow repeatedly under burst load, but small enough that the zero-initialization completes within SQL Server’s growth timeout. For SQL Server 2022+ instances, 64 MB is a useful ceiling because it qualifies for the partial IFI optimization. For older versions, choose an increment based on observed zero-initialization throughput on your storage.

-- Set a fixed 512 MB growth increment
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = [YourDatabase_Log], FILEGROWTH = 512MB);

Avoid percentage growth. There is no operationally defensible case for is_percent_growth = 1 on a production transaction log.

Address the underlying growth driver

If the log fills because log backups are failing, fix the backups. If a long-running transaction is holding the log open, find it with DBCC OPENTRAN and either let it complete or kill it. If replication or an AlwaysOn secondary is not consuming log fast enough, address the latency on that path. Adding log space without fixing the root cause only postpones the next stall.

See the SQL Server log_reuse_wait_desc and SQL Server log backups missing guides for the full root-cause tree.

Consolidate VLFs

If VLF count is already in the thousands, plan a log rebuild during a maintenance window. The pattern: take a log backup, shrink the log in small increments using DBCC SHRINKFILE, then grow it back to the target size in fixed chunks. This produces a small number of large VLFs instead of thousands of small ones. Do this once after fixing the autogrow configuration, not on a recurring schedule.

Warning: DBCC SHRINKFILE on the log is disruptive and generates heavy I/O on the volume. Never schedule it during peak load, and never run it on a recurring basis. Shrink-then-grow cycles are a one-time remediation, not maintenance.

When autogrow has already timed out

If the error log shows Msg 5144 followed by Error 9002, the database is no longer accepting writes. Immediate relief options, in order of preference: take a log backup if log_reuse_wait_desc permits; add a second log file on a faster volume as emergency capacity; switch the database to simple recovery model if losing point-in-time recovery between full and differential backups is acceptable. Switching to simple breaks the log chain since the last log backup and requires a full backup to restart it. None of these are good permanent fixes; each is a way to restore writes while you address the underlying sizing problem. See SQL Server Error 9002 for the full recovery procedure.

Prevention

• Pre-size log files at provisioning time. Treat autogrow as a break-glass mechanism. The log should be sized to span the longest expected gap between log backups.
• Set a fixed MB growth increment, never percentage. Choose a value that completes within SQL Server’s growth timeout on your slowest storage tier.
• Alert on autogrow events, not just disk space. Low disk is too late. Any Log File Auto Grow event in the default trace is a sign the log is undersized.
• Alert on Msg 5144 and Msg 5145. These mean autogrow is no longer completing within healthy timeframes. Treat them as capacity incidents.
• Monitor VLF count per database. Trend it over time. A growing VLF count means autogrow is happening in small increments.
• Verify the log backup chain. For full and bulk-logged recovery models, the most common cause of log growth is failed or missing log backups.
• Track log_reuse_wait_desc persistently. Any non-NOTHING value that persists across log backups is a growth driver that will eventually force autogrow.

How Netdata helps

• Per-second transaction log percent used makes the climb toward the next autogrow event visible long before the stall. Correlate the climb with batch requests per second to distinguish a workload burst from a stuck backup.
• Default-trace-based autogrow event capture turns Log File Auto Grow events into first-class time series. The event becomes a marker on the chart, not something you discover after the fact in the error log.
• Log file I/O latency from sys.dm_io_virtual_file_stats surfaces the storage-side contribution to the stall. If write latency on the log file is already elevated before the autogrow event, the stall will be longer.
• Wait statistics deltas expose PREEMPTIVE_OS_WRITEFILEGATHER and WRITELOG spikes in real time. The wait-type signature of an autogrow stall is distinctive.
• VLF count tracking per database flags the slow accumulation that turns one bad autogrow configuration into a recovery-time problem months later.
• Composite alerting across log used percent, autogrow events, and log_reuse_wait_desc distinguishes “log grew once due to a batch job” from “log is growing every few minutes because backups are broken.”

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server Error 9002: the transaction log for the database is full
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• SQL Server log backups missing: the full-recovery log that grows forever
• How Microsoft SQL Server actually works in production: a mental model for operators
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert