SQL Server recovery models and transaction log growth

If a SQL Server transaction log file keeps growing, the database is almost certainly in the full recovery model and nobody is taking log backups. In full recovery, the log can only be reused after a log backup has copied it somewhere, so without them every change since the database was created piles up in the .ldf file until the disk runs out. The fix is a decision, not a command: either you need point-in-time recovery, in which case you schedule BACKUP LOG every 15 to 60 minutes, or you do not, in which case you switch to the simple recovery model. Then shrink the log once, to a sensible size, and never again.

The rest of this guide explains why, so that you can make that decision properly and recognise the other, rarer reasons a log refuses to stop growing.

What the transaction log does#

Every change to a SQL Server database is written to the transaction log before it is considered committed. The data pages themselves are changed in memory and written to the data file later, at a checkpoint or when memory is needed. If the server stops unexpectedly, the log is what lets SQL Server replay committed work that had not reached the data file yet and undo work that had not committed. This is write-ahead logging, and it is why a SQL Server database survives a power cut.

Inside, the log file is divided into virtual log files (VLFs) and used in a circle. New records are written at the head; once every record in a VLF is no longer needed, that VLF can be marked reusable, which is called log truncation. Truncation does not make the file smaller. It only lets SQL Server write over space it already has. The file grows only when the head of the circle catches up with a VLF that is still needed.

So the question "why is my log so big?" is really "what is stopping truncation?". The recovery model decides the normal answer.

The three recovery models#

Simple suits most small application databases. The log only has to hold the currently active transactions, so it stays small by itself. The price is that you can restore only to the moment of your last full or differential backup: a nightly backup means up to a day of possible loss.

Full logs everything and keeps it until a log backup takes it. With a full backup on Sunday and log backups every 15 minutes, you can restore the database as it was at 14:31 on Thursday - one minute before someone ran a DELETE without a WHERE. That ability is the only reason to use full recovery, and without the log backups you get all of the cost and none of the benefit.

Bulk-logged is full recovery that minimally logs certain bulk operations - BULK INSERT, bcp loads with a table lock, SELECT INTO, index rebuilds. The log stays smaller during large loads, but a log backup containing a bulk operation can only be restored to its end, not to a point inside it. It is meant to be switched on around a maintenance window and back off afterwards, not left on.

Check every database at once, including what is holding each log:

SELECT name, recovery_model_desc, log_reuse_wait_descFROM sys.databasesORDER BY name;

New databases inherit their recovery model from the model system database. On Express, model is set to simple out of the box, so databases created there start in simple recovery; on Standard and Enterprise it is set to full. A database restored from another server keeps whatever model it had at the source, which is how a full-recovery database with no log backups ends up on a small Express instance.

Why the log will not truncate: log_reuse_wait_desc#

log_reuse_wait_desc names the reason the oldest part of the log is still needed. The values you will actually see:

LOG_BACKUP is by far the most common, and it is the case described at the top. ACTIVE_TRANSACTION is the second: one session opened a transaction and never closed it - an SSMS window with BEGIN TRAN and no COMMIT, an application that leaked a connection mid-transaction, a migration that is still running. Nothing written after that transaction began can be truncated, even in simple recovery. Find it:

DBCC OPENTRAN;SELECT s.session_id, s.login_name, s.host_name, s.program_name,       t.transaction_begin_timeFROM sys.dm_tran_active_transactions AS tJOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_idJOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_idORDER BY t.transaction_begin_time;

The oldest row is the one holding the log. Ask its owner, or KILL the session if it is clearly abandoned, which rolls the transaction back - and a rollback can take as long as the work did.

One more subtlety: a database switched to full recovery does not actually behave as full until its first full backup is taken. Until then there is no base for a log chain, so SQL Server truncates the log as if it were simple. People sometimes see a log stay small for weeks, then start growing the day the first full backup runs. That is the moment the log chain began.

How big is the log, and how full#

-- Every database: log size in MB and percentage usedDBCC SQLPERF(LOGSPACE);-- The current database in more detailSELECT total_log_size_in_bytes / 1048576.0 AS log_mb,       used_log_space_in_bytes / 1048576.0 AS used_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;-- Number of virtual log filesSELECT COUNT(*) AS vlf_count FROM sys.dm_db_log_info(DB_ID());

A large log that is 3 percent used is not a problem in itself - the space was needed once and will be reused. A log that is 99 percent used and still growing is the problem. The VLF count matters too: a log that grew in thousands of tiny increments ends up with thousands of VLFs, which slows down startup recovery and restores. A few hundred is unremarkable; tens of thousands is worth fixing by shrinking and regrowing the log in a few large steps.

When the log cannot grow any further - the disk is full, or a maximum size was set - SQL Server raises error 9002 and stops accepting changes to that database:

Msg 9002, Level 17, State 2The transaction log for database 'appdb' is full due to 'LOG_BACKUP'.

The message names the log_reuse_wait_desc reason, which tells you which fix applies. Reads keep working; writes fail until space is freed.

On Express this has a particular edge. The 10 GB per-database cap counts only data files, so the log can grow past it unnoticed, and on a hosted plan the log shares the plan's disk with the data and the backups. On a 10 GB or 20 GB disk, an unattended log in full recovery is a slow-motion outage.

Fix 1: switch to simple recovery#

If a nightly or more frequent full backup is an acceptable recovery point, switch to simple, let the log truncate, and shrink it once:

ALTER DATABASE [appdb] SET RECOVERY SIMPLE;CHECKPOINT;-- Find the log file's logical nameSELECT name, size / 128 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';-- Shrink it to 1 GB, then give it a sensible fixed growthDBCC SHRINKFILE (N'appdb_log', 1024);ALTER DATABASE [appdb]MODIFY FILE (NAME = N'appdb_log', FILEGROWTH = 256MB);

If the shrink does not get the file down, the active part of the log is at the end of the file; run CHECKPOINT and the shrink again after a few minutes. Size the log for your largest regular transaction - often an index rebuild or a nightly import - rather than as small as possible. A log that shrinks to 100 MB only to grow back to 2 GB every night is doing pointless work, and each growth pauses writes while the new space is zeroed. SQL Server 2022 can skip that zeroing for log growths of up to 64 MB, but larger growths are still zero-initialised.

Shrinking the log once after fixing the cause is fine. Shrinking it on a schedule is a sign the cause was never fixed. Shrinking data files on a schedule is worse, because it fragments every index it touches.

Fix 2: stay in full recovery and back up the log#

If losing up to a day of data is not acceptable, keep full recovery and back up the log often enough that it never grows large:

BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH CHECKSUM, INIT;

Each log backup copies the log since the previous one and lets that part truncate. Every 15 minutes is common; the interval is your maximum data loss if the server is lost along with its log. Name each file by time, never overwrite them, and keep every log backup back to the oldest full backup you keep - a chain with one missing file cannot be restored past the gap.

Express has no SQL Server Agent, so the schedule runs outside the engine: a sqlcmd call from cron on another machine, with a script that builds the file name from the date and time, exactly as for full backups. SQL Server backup and restore has the script, and sqlcmd and bcp explains the -b flag that makes failures visible. Log backups are only useful if they leave the server too, so copy them off as they are taken.

Restoring to a point in time#

This is what full recovery buys. Suppose a bad DELETE ran at 14:32. First take a tail-log backup, which captures everything up to now and leaves the database in the restoring state so nothing else can change it:

USE master;BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH NORECOVERY;

Then restore the last full backup, any differential, and every log backup in order, stopping just before the mistake:

RESTORE DATABASE [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-full.bak'WITH NORECOVERY, REPLACE;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1400.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH STOPAT = '2026-10-08T14:31:30', RECOVERY;

Often it is better to restore to a new database name beside production and copy back only the deleted rows, so that everything written after 14:32 by other users is kept. The msdb backup history lists every log backup with its time range, which is how you find the right files when there are hundreds. Practise this once on a copy before you need it; testing a restore before you need it makes the case.

Recovery models and host backups#

Hosted servers usually come with some kind of backup of their own, and it is worth being clear about how it relates to the recovery model, because the two answer different questions.

A host-level backup copies the server's files at a moment, and restoring it takes the whole server back to that moment. It knows nothing about log chains. It cannot restore one database to 14:31, and it is only as good as its schedule: a nightly copy is a nightly recovery point, whatever recovery model the database is in. A database in full recovery with no log backups gains nothing from the host's backup except a bigger log to copy.

Native backups are the layer that understands SQL Server. A full backup gives a consistent database, a differential shortens the restore, and log backups give the minute-level recovery point. A practical combination for a small database is: simple recovery, a native full backup every night written a few minutes before the host's own scheduled backup so the archive always contains a consistent .bak, and a copy of that file downloaded somewhere else every week. If you need better than a daily recovery point, switch to full recovery and add log backups that leave the server as they are taken.

On RE:NODE, SQL Server plans come with one to four backup slots depending on the tier, taken on demand or on a schedule from the Schedules tab and stored off the machine they protect. They are whole-server backups in exactly the sense above. The database created for you is yours to configure, so check its recovery model with the query near the top of this guide on the first day rather than after the disk fills.

Habits that keep the log small#

The recovery model sets the baseline. Day-to-day log size is set by the biggest single transaction, because an open transaction pins all the log written since it started.

• Delete in batches. One DELETE of ten million rows is one transaction that must fit in the log. A loop deleting 5,000 rows at a time lets the log truncate between batches - after a checkpoint in simple recovery, or after the next log backup in full.
• Keep transactions short. Do not hold a transaction open while waiting for a user, an HTTP call or a file upload.
• Watch index maintenance. Rebuilding a large index is fully logged in full recovery and can generate a log as big as the index. Rebuild only what needs it, which on NVMe is very little; SQL Server indexes and execution plans has the reasoning.
• Set growth in megabytes, not percent. Ten percent of a small log is tiny and fragments it into many VLFs; ten percent of a huge one is a long pause.

WHILE 1 = 1BEGIN    DELETE TOP (5000) FROM dbo.Events WHERE CreatedAt < '2026-01-01';    IF @@ROWCOUNT = 0 BREAK;END;

FAQ#

Is it safe to delete the .ldf file to free space?

No. The log is part of the database, and deleting it while it holds active transactions can leave the database unrecoverable or force a repair that loses data. Fix the cause of growth, then shrink it with DBCC SHRINKFILE.

Should I use simple or full recovery for a small web app?

Simple, unless losing the data written since the last backup would genuinely hurt. Most small apps take a nightly full backup and accept that window. If you choose full, schedule log backups the same day, or the log will grow without limit.

Why did my log grow even in simple recovery?

Because something held a transaction open, or one statement changed a huge number of rows. In simple recovery the log still has to hold every active transaction in full. Check log_reuse_wait_desc and DBCC OPENTRAN for the culprit.

Does the transaction log count towards the Express 10 GB limit?

No. The limit applies to the data files of each database. The log still uses disk, though, and on a hosted plan it shares space with the data and any backups stored on the server, so it needs watching just the same.

How often should log backups run?

As often as the amount of work you are willing to lose. Every 15 minutes is a common default; every 5 suits busy systems. More frequent backups also keep each file small and the log itself compact.