A transaction log that keeps growing: the SIMPLE and FULL recovery models

The disk holding the log files on srv-sql-01 is running out of space. The CrmDB database in the CRM instance has a 5 GB data file and an 80 GB log file, CrmDB_log.ldf. The application suddenly stops saving changes, and the SQL Server error log shows error 9002 with the message The transaction log for database 'CrmDB' is full due to 'LOG_BACKUP'. Queries that read data still work, but no change will go through until space is freed in the log.

A log file like this is almost never the result of a failure, but of the FULL recovery model without log backups: SQL Server keeps every change until it has gone into a log backup. This post explains how the log works, how to read the cause from the log_reuse_wait_desc column, and how to bring the file back to a sensible size without losing data.

How the transaction log works

Every change in the database goes to the transaction log first and only later to the data file. The log is usually a single .ldf file, which SQL Server divides internally into virtual log files (VLFs). Their number and size are not set manually: they are created when the file is created and at every growth.

New records are appended at the end of the log. The section from the oldest record still needed to recover the database after a failure (MinLSN) to the last written record is the active part of the log. Its start is determined, among other things, by the oldest open transaction. VLFs lying entirely before MinLSN are inactive, but they can be written to again only after log truncation. When writing reaches the end of the file, it wraps around to the start, to the freed VLFs. If no VLF is free, SQL Server grows the file by the FILEGROWTH value. When it cannot, because the disk is out of space or growth is disabled, it raises error 9002.

SIMPLE versus FULL

The recovery model decides what truncates the log and up to which moment the database can be restored.

Under the SIMPLE recovery model, the log is truncated automatically after a checkpoint. Log backups are not possible, so the database can only be restored to the time of the last full or differential backup. With a nightly full backup, a failure at 4 pm means losing a whole working day of changes.

Under the FULL recovery model, the log is truncated only after a log backup (BACKUP LOG), provided a checkpoint has occurred since the previous backup. A full backup does not truncate the log. In return, an unbroken sequence of log backups allows the database to be restored to a chosen moment, for example to the minute before an accidental DELETE without a WHERE clause, and the loss is limited to changes since the last log backup.

The third model, BULK_LOGGED, is a variant of FULL with minimal logging of bulk operations, which also requires log backups but does not allow point-in-time restore for log backups that contain bulk operations.

Aspect	SIMPLE	FULL
What truncates the log	a checkpoint	a log backup
Log backups	not possible	required
Possible data loss	changes since the last full or differential backup	changes since the last log backup
Point-in-time restore	no	yes, RESTORE LOG … WITH STOPAT
Log shipping, availability groups	no	yes
Default in edition	Express	Standard and Enterprise

Choosing the model is a decision about how much work the business can afford to lose, not a technical setting. An application configuration database that changes once a week can run under SIMPLE with a nightly full backup. The CrmDB database, which receives orders all day, usually needs FULL and log backups every 15–30 minutes.

Why a database in FULL grows without limit

In the Standard and Enterprise editions, the model system database uses FULL, and every new database inherits its recovery model from it. Right after creation, a database in FULL behaves as if it were in SIMPLE: until the first full backup exists, the log is reused without log backups. Then an administrator sets up a nightly full backup, and from that moment the log backup chain begins. Nobody scheduled log backups, so no VLF is freed any more, and the file grows with every working day, every nightly index rebuild and every import. The full backups succeed, the backup job reports look fine, and the disk fills up over several months.

There are two ways out: schedule log backups, or deliberately switch the model to SIMPLE. How to take log backups and run them on a schedule without SQL Agent is covered in the sixth part of this guide.

log_reuse_wait_desc: what the log is waiting for

The log_reuse_wait_desc column in the sys.databases view shows what was blocking log reuse as of the last checkpoint, and the same value appears in the error 9002 message after the words full due to.

log_reuse_wait_desc value	Cause	What to do
NOTHING	there are free VLFs in the log	nothing; an oversized file can be shrunk once
CHECKPOINT	no checkpoint since the last truncation	usually clears by itself; if needed, CHECKPOINT in the database context
LOG_BACKUP	a database in FULL or BULK_LOGGED is waiting for a log backup	a log backup and a fixed schedule, or a deliberate switch to SIMPLE
ACTIVE_BACKUP_OR_RESTORE	a data backup or a restore is running	wait for it to finish; check that the backup is not stuck
ACTIVE_TRANSACTION	a long-running or abandoned transaction, under any model	find it with DBCC OPENTRAN, commit or roll it back, KILL as a last resort
REPLICATION	transactional replication or CDC has not read the changes yet	check that the Log Reader Agent or the CDC jobs are running
AVAILABILITY_REPLICA	an availability group secondary replica has not hardened or applied the log	check the send and redo queues on the replicas

In a database with regular log backups, LOG_BACKUP is normal and only means that new records have arrived since the last backup. It becomes a problem only when there are no log backups or the intervals are longer than planned.

What to do right away

If the database is to stay in FULL, the first step is a log backup. The backup takes roughly as much space as the used part of the log, so the target disk may need to hold several tens of gigabytes. If the log has never been backed up before, the first backup may take a long time, and freeing the end of the file before shrinking it sometimes requires another log backup.

-- A log backup truncates the log; the .trn file becomes part of the backup chain
BACKUP LOG [CrmDB] TO DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_101500_LOG.trn' WITH CHECKSUM;

If the business accepts losing changes since the last full backup, the database can deliberately be switched to SIMPLE. Before switching, take one last log backup if there is space for it. The change breaks the log backup chain: point-in-time restore is no longer possible, and any scheduled log backups of this database must be disabled.

USE [master];
ALTER DATABASE [CrmDB] SET RECOVERY SIMPLE;
GO
USE [CrmDB];
-- Under SIMPLE, a checkpoint truncates the log
CHECKPOINT;

Once log_reuse_wait_desc shows NOTHING, the file can be shrunk. The target size is not zero but as much as the log needs in the busiest period between log backups, for example during the nightly index rebuild or the largest import. For the database in the example, let us assume 4 GB. When tidying up a log, Microsoft recommends shrinking it first and then growing it to the target size in a single step, which also produces a sensible number of VLFs.

USE [CrmDB];
-- Target size in MB; the log shrinks only to a VLF boundary
DBCC SHRINKFILE (N'CrmDB_log', 1024);
-- Grow to the target size in a single step
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB_log', SIZE = 4096MB);

If DBCC SHRINKFILE does not reach the requested size, the active part of the log sits at the end of the file. After the next log backup (under SIMPLE, after a checkpoint), run the command again.

Shrinking the log should be a one-off. A nightly job with DBCC SHRINKFILE or the AUTO_SHRINK option turned on is a vicious circle: the file grows again under the next load, every growth slows down writes, and the number of VLFs keeps rising. Microsoft explicitly advises against treating file shrinking as routine maintenance.

Growth in megabytes, not percentages

Percentage growth grows with the file: at 10%, an 80 GB file grows by 8 GB in one go. In versions earlier than SQL Server 2022 (16.x), new log space is always zeroed, and writes to the database wait until that is done. Starting with SQL Server 2022, log growths of up to 64 MB benefit from instant file initialisation, while larger ones still have to be zeroed.

The growth size also determines the number of VLFs: a growth smaller than 1/8 of the file size creates one VLF, a larger one creates 4 VLFs below 64 MB (from SQL Server 2022, one VLF up to and including 64 MB), 8 VLFs up to 1 GB and 16 VLFs above that. Many small growths produce a very large number of VLFs, which slows down database startup, restores and log backups. Microsoft recommends keeping their number to a few thousand at most.

Sensible values are a fixed log growth in megabytes: 64 MB for small logs, 256–512 MB for logs of several gigabytes, and no more than 1024 MB, which is the upper limit Microsoft recommends. Since SQL Server 2016 (13.x), the default growth of new files is 64 MB. Values of 10% and 1 MB remain in databases created in older versions, because file settings travel with the database when it is migrated through backup and restore.

-- Growth in MB instead of a percentage, separately for the log and the data
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB_log', FILEGROWTH = 512MB);
ALTER DATABASE [CrmDB] MODIFY FILE (NAME = N'CrmDB', FILEGROWTH = 256MB);

The model database as a template for new databases

Every new database inherits its options from the model database, including the recovery model, as well as the initial size of the data file. The setting of model is therefore a decision for all future databases on the instance. If the instance has no log backups, model should use SIMPLE. If log backups automatically cover every database in FULL, FULL is safe.

-- Applies to databases created after the change; existing ones stay as they are
ALTER DATABASE [model] SET RECOVERY SIMPLE;

Databases restored from a backup or attached to the instance keep their own recovery model, so check it after every migration. File size and growth are best specified explicitly in the database creation script. The Express edition uses SIMPLE by default; when that edition is enough is covered in the seventh part of this guide.

Check your own environment

The recovery model and log reuse wait of every database on the instance. A good result is NOTHING or CHECKPOINT, and LOG_BACKUP only in databases with regular log backups. A bad result is LOG_BACKUP in a database without log backups (the third block checks this) or ACTIVE_TRANSACTION that persists for hours.

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

Log usage for all databases and details for one database. An 80 GB file that is only a few per cent used is a log that grew at some point and can be shrunk once. Usage above 90% with LOG_BACKUP means error 9002 will appear as soon as there is no room for the next growth. The since_last_backup_mb column shows how much log has accumulated since the last log backup.

-- All databases: log size in MB and percentage used
DBCC SQLPERF (LOGSPACE);

-- Current database: size, usage and log written since the last log backup
USE [CrmDB];
SELECT total_log_size_in_bytes / 1048576 AS log_size_mb,
       used_log_space_in_bytes / 1048576 AS used_mb,
       used_log_space_in_percent,
       log_space_in_bytes_since_last_backup / 1048576 AS since_last_backup_mb
FROM sys.dm_db_log_space_usage;

The last full backup and the last log backup of every database, set against its recovery model. A bad result is a database in FULL with a date in last_full and an empty last_log, or one older than the planned interval, which is exactly the scenario from the start of this post. For databases in SIMPLE, an empty last_log is correct.

SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.recovery_model_desc, d.name;

File growth and VLF count (the sys.dm_db_log_info function is available from SQL Server 2016 SP2). A good result is growth in MB, at most 1024 MB for the log, and a VLF count in the tens or hundreds. A bad result is percentage growth, disabled growth (error 9002 as soon as the log fills up) or several thousand VLFs or more, meaning a log that grew in small steps.

-- growth is in 8 KB pages when is_percent_growth = 0
SELECT DB_NAME(database_id) AS database_name, name, type_desc,
       size / 128 AS size_mb,
       CASE WHEN growth = 0 THEN 'autogrowth off'
            WHEN is_percent_growth = 1 THEN CONCAT(growth, ' %')
            ELSE CONCAT(CAST(growth / 128.0 AS decimal(10, 2)), ' MB') END AS growth_setting
FROM sys.master_files
ORDER BY database_name, type_desc;

-- VLF count in the log of every available database
SELECT d.name, COUNT(*) AS vlf_count
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS l
WHERE d.state_desc = 'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;