Why SQL Server transaction logs keep growing

A transaction log that keeps growing is one of the most common reasons a SQL Server ends up in trouble. The drive fills, the database stops accepting changes with error 9002, and someone reaches for a shrink command. The growth is almost always a symptom. SQL Server will tell you what the log is waiting for, and once you know that, the fix is usually straightforward and need not put your backups at risk.

How the transaction log works

Every change is written to the transaction log before it reaches the data files. That is what lets SQL Server roll back incomplete transactions, recover after a crash and restore to a point in time. The log is designed to be reused, not to grow indefinitely as a history file.

Each physical log file is divided internally into virtual log files (VLFs). SQL Server fills one VLF after another and, at the end of the file, wraps round to the start – provided the earlier VLFs are no longer needed. Marking them reusable is called log truncation. Despite the name, truncation does not make the file smaller; it frees space inside it.

A VLF can be reused only when nothing still needs any record in it. Anything that pins the oldest required record, such as an open transaction, a pending log backup or a replica that has not caught up, stops truncation. New records then need fresh space, so the file grows until autogrowth is disabled, a maximum size is reached or the disk fills, and error 9002 follows.

Recovery models, and why FULL without log backups keeps growing

• SIMPLE: the log is truncated automatically after a checkpoint. There are no log backups, so you can restore only to the end of a full or differential backup.
• FULL: the log is truncated only by a log backup, and not by a copy-only one. In return, you can restore to any point covered by an unbroken chain of log backups.
• BULK_LOGGED: like FULL, but some bulk operations, such as BULK INSERT and index rebuilds, can be minimally logged. Log backups are still required, and Microsoft documents point-in-time recovery as unsupported, so it is normally used only temporarily.

New databases take their recovery model from model, which is FULL on Enterprise and Standard editions. If nobody schedules log backups, nothing truncates the log and it grows until it hits a limit, which makes this one of the most common causes of a runaway log. One twist catches people out: a new FULL database reuses its log as if it were SIMPLE until its first full backup. The log can stay small for months, then start growing the week someone adds a nightly full backup without log backups.

If the database needs point-in-time recovery, schedule log backups at a frequency that matches how much work the business can afford to lose. If it does not, SIMPLE may be right, but make that a deliberate recovery decision, not a way to silence a disk alert.

Finding out what the log is waiting for

Start with log_reuse_wait_desc in sys.databases. It reflects the last checkpoint, so it can lag slightly.

SELECT name,
       recovery_model_desc,
       log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

• NOTHING or CHECKPOINT: normal.
• LOG_BACKUP: a log backup is needed before space can be reused. Log backups are missing, failing or too infrequent.
• ACTIVE_TRANSACTION: a long-running transaction is holding the log. This applies in every recovery model, including SIMPLE.
• REPLICATION: transactional replication or change data capture (CDC) has not processed the log. Check the Log Reader Agent or CDC capture job, and look for publications or CDC configuration left behind with no working agent.
• AVAILABILITY_REPLICA: an availability group secondary has not caught up, in either synchronous or asynchronous commit mode. Look for a suspended, disconnected or slow secondary, and check log_send_queue_size and redo_queue_size in sys.dm_hadr_database_replica_states.
• ACTIVE_BACKUP_OR_RESTORE: a data backup or restore is running. Log backups can still run during a slow full backup, but the log cannot be cleared past the point that backup needs until it finishes.

To see how full the log is, run this in the database concerned:

SELECT DB_NAME(database_id) AS database_name,
       total_log_size_in_bytes / 1048576.0 AS log_size_mb,
       used_log_space_in_bytes / 1048576.0 AS log_used_mb,
       used_log_space_in_percent,
       log_space_in_bytes_since_last_backup / 1048576.0 AS used_since_log_backup_mb
FROM sys.dm_db_log_space_usage;

A large file with a low percentage used is not an emergency; the space will be reused. A high percentage used with a wait other than NOTHING or CHECKPOINT is what to act on. These DMVs need VIEW SERVER STATE, or the finer-grained performance state permissions introduced in SQL Server 2022.

Long-running and open transactions

For ACTIVE_TRANSACTION, find the transaction first. DBCC OPENTRAN reports the oldest active transaction in a database, with its session ID and start time:

DBCC OPENTRAN ([YourDatabase]) WITH TABLERESULTS, NO_INFOMSGS;

For every database, with log usage and the source of each transaction:

SELECT st.session_id,
       DB_NAME(dt.database_id) AS database_name,
       dt.database_transaction_begin_time,
       DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
       dt.database_transaction_log_bytes_used / 1048576.0 AS log_used_mb,
       dt.database_transaction_log_bytes_reserved / 1048576.0 AS log_reserved_mb,
       es.host_name,
       es.program_name,
       es.login_name
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = dt.transaction_id
JOIN sys.dm_exec_sessions AS es
    ON es.session_id = st.session_id
WHERE dt.database_transaction_begin_time IS NOT NULL
ORDER BY dt.database_transaction_begin_time;

Typical culprits are an application that never commits, a query window left open after BEGIN TRAN, a large delete or load run as one transaction, and index maintenance on large tables. The host, program and login columns usually tell you who to speak to.

Be careful with KILL. The transaction rolls back, a large rollback can take a long time, and the log stays pinned until it completes. Where possible, have the owner finish or cancel the work, and break legitimate large operations into smaller batches.

SQL Server 2019 and later offer accelerated database recovery (ADR), which truncates the log aggressively even during long transactions. It is off by default, stores row versions in the database, and its aggressive truncation is disabled when transactional replication or CDC is in use. It is worth evaluating, but it does not replace dealing with the transaction in front of you.

Shrinking, growth settings and VLF counts

Shrinking returns unused log space to the operating system. Once, after a one-off event, that can be reasonable. As a routine answer to growth it fails:

• It does not touch the cause, so the file barely shrinks or grows straight back.
• Growth is expensive. Before SQL Server 2022, log growth cannot use instant file initialisation, so new space is zeroed and the database can pause meanwhile. From SQL Server 2022, only increments of 64 MB or less benefit.
• Repeated small shrink and grow cycles leave the log split into very many VLFs.

Microsoft’s guidance is that shrinking should not be regular maintenance and AUTO_SHRINK should stay off. Size the log for its real peak, usually set by the largest index maintenance job, the largest batch or the log generated during a full backup, and leave it there.

Set autogrowth as a fixed size rather than a percentage. Microsoft recommends no more than 1,024 MB for log files, and the default since SQL Server 2016 is 64 MB. Each growth adds VLFs. From SQL Server 2014, a growth smaller than one eighth of the current log size adds one VLF. Otherwise:

Size of growth	VLFs created
Less than 64 MB (64 MB or less in SQL Server 2022 and later)	4 (1 in SQL Server 2022 and later)
64 MB up to 1 GB	8
More than 1 GB	16

A log grown to 50 GB in 10 MB steps ends up with thousands of small VLFs. Too many VLFs slows startup, restores and log backups, and delays replication, CDC and availability group redo. Microsoft suggests keeping the total to several thousand at most, with serious symptoms at hundreds of thousands.

SELECT d.name AS database_name,
       COUNT(*) AS vlf_count,
       SUM(CASE WHEN li.vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlfs
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;

sys.dm_db_log_info requires SQL Server 2016 SP2 or later. Where a count is excessive, the remedy is one planned shrink, growing the log back to its working size in one or a few large steps, and correcting autogrowth – in a quiet period, with a recent restorable backup, after the original cause is fixed.

What to do when the disk is nearly full

The aim is to buy space safely and remove the cause without losing the ability to recover.

1. Read the wait for the affected database, and check file sizes, growth settings (0 means autogrowth is off) and free space with the query below.
2. For LOG_BACKUP, take a log backup using the existing job, or to a location with space, ideally not the filling volume. Keep the file with the rest of the chain, because any restore past this point needs it. Occasionally a second log backup is needed before space is released. Then find out why scheduled backups stopped.
3. For ACTIVE_TRANSACTION, REPLICATION or AVAILABILITY_REPLICA, resolve the transaction, restart the stalled agent, or resume or repair the replica.
4. Buy headroom if needed by moving unrelated files, extending the volume or, temporarily, adding a second log file on another volume and removing it once it is no longer in use.
5. Tidy up afterwards: consider a one-off shrink, check the VLF count and set autogrowth properly.

SELECT DB_NAME(mf.database_id) AS database_name,
       mf.name AS logical_file_name,
       mf.size / 128 AS size_mb,
       CASE WHEN mf.is_percent_growth = 1 THEN mf.growth END AS growth_percent,
       CASE WHEN mf.is_percent_growth = 0 THEN mf.growth / 128 END AS growth_mb,
       vs.volume_mount_point,
       vs.available_bytes / 1048576 AS volume_free_mb
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE mf.type_desc = N'LOG'
ORDER BY volume_free_mb, size_mb DESC;

Common emergency moves that make things worse:

• Switching to SIMPLE and back. It breaks the log backup chain: point-in-time recovery is lost, and log backups fail, until a new full or differential backup is taken. If it has already happened, take that backup now.
• Backing up the log to NUL. Space is freed, but the log records are discarded, leaving a gap no restore can cross.
• Deleting the log file, or detaching the database to get rid of it. The log is needed to keep the database consistent, and removing it can leave the database unrecoverable.
• Scheduling a shrink job. It hides the symptom until the disk fills again.

If a log keeps growing and the cause is not clear, or the disk is close to full on a system the business depends on, talk to a SQL Server specialist before running anything that changes the database. The queries above are where we start, and the aim is always the same: free the space, fix the cause and keep the backup chain intact.