SQL Server Transaction Log Too Big? Causes, Diagnosis and Safe Fixes

When a SQL Server database’s log file (.ldf) is bigger than its data files, or starts filling the disk, something is usually wrong. Most DBAs’ first reflex is to shrink the log. But a shrink done without understanding why the log grew only postpones the problem for a few days, and in some cases it creates a bigger risk by breaking the backup chain.

This article explains step by step why the SQL Server transaction log grows, how to diagnose the real cause, how to shrink the log safely, and how to prevent the problem permanently with the right size and autogrowth settings.

The Key Concept: Truncation and Shrink Are Different Things

Most transaction log problems come from confusing these two:

• Log truncation makes the part of the log that’s no longer needed reusable. It is a logical operation and doesn’t change the file’s size on disk. It happens automatically after a checkpoint under the simple recovery model, and after a log backup under the full and bulk-logged models.
• Log shrink returns free space at the end of the log file to the operating system, reducing the file’s physical size. It only helps when the file actually contains free space.

Why Does the Transaction Log Grow?

The log keeps growing as long as it can’t be truncated. SQL Server tells you exactly why for every database, in the log_reuse_wait_desc column of sys.databases. The most common values are:

The log also grows during large one-off operations: a big index rebuild, a batch that updates millions of rows in a single transaction, or a data load. Once the operation finishes the log is truncated, but the file stays at its grown size. In that case log_reuse_wait_desc usually shows NOTHING and most of the log is empty.

Step 1: Which Databases Have a Log Problem?

This query shows, for every database on the server, the log size, how full the log is, the total size of the data files, and why the log can’t be truncated:

DECLARE @logspace TABLE (
    database_name sysname,
    log_size_mb   decimal(18, 2),
    log_used_pct  decimal(9, 2),
    status        int
);

INSERT INTO @logspace
EXEC (N'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

SELECT d.name,
       d.recovery_model_desc,
       d.log_reuse_wait_desc,
       ls.log_size_mb,
       ls.log_used_pct,
       df.data_size_mb
FROM sys.databases AS d
JOIN @logspace AS ls ON ls.database_name = d.name
CROSS APPLY (SELECT SUM(CAST(mf.size AS bigint)) * 8 / 1024 AS data_size_mb
             FROM sys.master_files AS mf
             WHERE mf.database_id = d.database_id
               AND mf.type = 0) AS df
ORDER BY ls.log_size_mb DESC;

Read the results like this:

• The log is large and full (high log_used_pct): something is preventing truncation. Apply the matching fix below based on log_reuse_wait_desc. A shrink won’t help here.
• The log is large but mostly empty (low log_used_pct): the log grew because of a one-off operation. If it isn’t expected to grow again, a controlled shrink is fine.
• The log is bigger than the data files: not always a mistake on its own, but almost always worth investigating.

For more detail inside a single database, you can also use sys.dm_db_log_space_usage, which additionally shows how much log has been generated since the last log backup.

Step 2: Fix the Cause

LOG_BACKUP: no log backups are running

In a database using the full recovery model, the log is only truncated when a log backup is taken. If nobody takes log backups, the log grows until the disk is full. The fix is regular log backups. Microsoft notes that a log backup every 15 to 30 minutes may be enough for most systems; more frequent backups reduce both your data loss exposure and the log size.

BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG_20261006_1100.trn'
WITH COMPRESSION, CHECKSUM;

If the log has never been backed up before, Microsoft notes that you may need to take two log backups before the log can be truncated. Schedule these backups with a SQL Server Agent job and monitor that the job actually runs.

If the database doesn’t need point-in-time recovery (a staging database, for example), the right fix may be the simple recovery model rather than log backups. We explain how to make that decision in SQL Server Simple Recovery Model. But switching a production database to simple just to shrink the log breaks the log chain and silently removes your point-in-time restore capability.

ACTIVE_TRANSACTION: a transaction left open

First, find the oldest open transaction:

-- Oldest active transaction
DBCC OPENTRAN (N'SalesDB');

-- Open transactions in the database, with session details and log used
SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       dt.database_transaction_begin_time,
       DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
       dt.database_transaction_log_bytes_used / 1048576 AS log_used_mb
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s ON st.session_id = s.session_id
WHERE dt.database_id = DB_ID(N'SalesDB')
ORDER BY dt.database_transaction_begin_time;

The permanent fix is usually in the application: split large updates and deletes into smaller batches, and don’t keep transactions open longer than necessary. Accelerated Database Recovery (ADR), available from SQL Server 2019, also greatly reduces the impact of long transactions on recovery and rollback time.

AVAILABILITY_REPLICA and REPLICATION

Here the log is being held because a feature that depends on it hasn’t consumed the data yet. For Always On, check the connection and synchronization state of the secondary replicas; for replication, check the Log Reader Agent and the distribution database. A suspended AG database or a stopped replication agent can make the log grow for days. For Always On architecture, see our SQL Server Always On guide.

Emergency: the log disk is full (Error 9002)

When the log fills up, SQL Server raises error 9002 and the database stops accepting writes. Do the diagnosis above first. If the log can’t grow because the disk is full, Microsoft’s safe options are:

• Free space on the same disk by moving or deleting other files.
• Move the log file to a larger disk.
• Add a temporary second log file on another disk (ALTER DATABASE ... ADD LOG FILE), and remove it once the root cause is fixed.

Step 3: Shrink the Log Safely

Once the root cause is fixed and the log has been truncated, a one-time, controlled shrink is fine if the file is still larger than it needs to be:

USE [SalesDB];

-- 1) In full recovery: take a log backup first so the log is truncated
BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG_before_shrink.trn'
WITH COMPRESSION, CHECKSUM;

-- 2) Find the logical name and current size of the log file
SELECT name, size / 128 AS size_mb, growth, is_percent_growth
FROM sys.database_files
WHERE type = 1;

-- 3) Shrink the log to a target size (MB)
DBCC SHRINKFILE (N'SalesDB_log', 1024);

-- 4) Grow it back to the real requirement in one step, with a fixed autogrowth
ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB_log', SIZE = 8192MB, FILEGROWTH = 512MB);

Things to keep in mind:

• A shrink can only remove free sections at the end of the log file. If the last part of the file is still in use, the shrink won’t reach the target size. Taking another log backup and trying again usually solves it.
• Don’t shrink the log below the size it will reach again under normal workload. Microsoft advises against shrinking files that grow because of regular business operations: when the log grows again, transactions slow down while it grows.
• Don’t put shrink into a maintenance plan or a scheduled job, and don’t enable AUTO_SHRINK. Microsoft is explicit that shrink shouldn’t be treated as routine maintenance. For details, see Best Practices for SQL Server Shrink.

VLFs: The Log’s Hidden Performance Problem

SQL Server divides each log file internally into virtual log files (VLFs), and every autogrowth event creates new ones. According to the rule Microsoft documents:

• If the growth is less than 1/8 of the current log size (SQL Server 2014 and later), a single VLF is created.
• Otherwise, growths under 64 MB create 4 VLFs, growths from 64 MB to 1 GB create 8 VLFs, and growths over 1 GB create 16 VLFs. From SQL Server 2022, growths up to 64 MB create a single VLF.

A log that has grown for years in small steps (for example with the old defaults of 10% or 1 MB) can end up with tens of thousands of VLFs. Microsoft notes that a large number of VLFs slows database startup, log backups and restores, and causes latency in replication, CDC and Always On redo. To check your VLF counts:

SELECT d.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;

Microsoft recommends keeping the total number of VLFs to a few thousand at most. If you see a very high count, the fix is the same as the shrink steps above: shrink the log, then grow it back to the required size in a few large steps. For more detail, see How High VLF Counts Kill Your SQL Server Performance.

The Right Log Size and Autogrowth Settings

The permanent way to prevent the problem is to size the log correctly from the start. Microsoft’s recommendations can be summarized as follows:

• Size the log for its busiest period. Monitor log usage during a full backup (log backups can’t truncate the log while it runs), during your largest index maintenance, and during the largest batch in the database.
• Set autogrowth as a fixed size in MB, not a percentage. Percentage growth produces ever-larger, unpredictable growth steps as the log grows.
• Don’t set log FILEGROWTH above 1,024 MB. Since SQL Server 2016, the default for new databases is 64 MB. From SQL Server 2022, log growths up to 64 MB benefit from instant file initialization and complete almost instantly.
• Multiple log files don’t improve performance. Log files are used sequentially; a second log file is only ever a temporary space fix.
• Put the log on a fast, separate disk. Log writes are sequential and every commit waits for them, so log disk latency translates directly into transaction time. See Best Practices for Disk Storage in SQL Server.

Catch Log Growth Before It Becomes an Outage

It’s much better to be alerted when a threshold is crossed than to find out the log is full from user complaints. SQL Server Agent performance condition alerts can monitor how full the log is:

-- Alert when the SalesDB log is more than 80% full
-- (On a named instance the object name is 'MSSQL$InstanceName:Databases')
EXEC msdb.dbo.sp_add_alert
     @name = N'SalesDB - Percent Log Used > 80',
     @performance_condition = N'SQLServer:Databases|Percent Log Used|SalesDB|>|80',
     @delay_between_responses = 900;

EXEC msdb.dbo.sp_add_notification
     @alert_name = N'SalesDB - Percent Log Used > 80',
     @operator_name = N'DBA Team',
     @notification_method = 1;

-- Alert when the log is completely full (Error 9002)
EXEC msdb.dbo.sp_add_alert
     @name = N'Error 9002 - Transaction Log Full',
     @message_id = 9002,
     @severity = 0,
     @delay_between_responses = 300;

We explain how to build the alerting setup properly in SQL Server Alerts: Which Ones to Enable and How to Set Them Up Right. Error 9002 is also written to the SQL Server error log.

Frequently Asked Questions