Log Truncation vs. Log Shrinking: What Each One Actually Does

The one-sentence answer

Truncation frees space inside the log file. Shrinking makes the log file smaller.

Truncation happens automatically, is what you want, and does not change the size of anything on disk. Shrinking is something you do by hand, gives disk space back to Windows, and is almost never the right response to a log problem.

The car park

Picture the transaction log as a multi-storey car park with a fixed number of spaces. The building is the log file. Each space is a chunk of log, and SQL Server fills them in order, then wraps around to the beginning and reuses the ones that have been freed.

• Truncation is cars driving out. Spaces become available again. The building does not change size, and from the street it looks exactly the same. Nothing is deleted, nothing is written to disk, a flag is flipped that says "this part can be overwritten now."
• Shrinking is demolishing the top floors. The building physically gets smaller. The land underneath goes back to the city, which is Windows.
• "The log is full" means every space is occupied and no car is allowed to leave. Demolishing floors does not help with that. Something is holding the cars in place, and until you find out what, a smaller car park just fills up faster.

Which leads to the mistake that this entire article exists for: people respond to a full log by shrinking it. That is demolishing floors of a building that is full because the exits are blocked.

What truncation really is

The log file is divided internally into virtual log files, usually called VLFs. Truncation is SQL Server marking one or more VLFs as inactive, which means their content is no longer needed and the space can be written over.

A VLF can only be marked inactive once nothing still needs it. When it happens depends on the recovery model:

That middle row is responsible for most emergency calls about a log filling a disk. The database is in FULL recovery, somebody set up nightly full backups and no log backups at all, and the log has been growing since the day the database went live because nothing has ever told SQL Server the log records are safe to discard.

A full backup does not truncate the log. If you are in FULL recovery and never take log backups, you are in FULL recovery in name only, and you are paying for it in disk space.

The query that tells you the actual cause

Before touching anything, ask SQL Server why it cannot reuse the log. There is a column for exactly this:

SELECT name,
       recovery_model_desc,
       log_reuse_wait_desc
FROM   sys.databases
WHERE  name = 'AppDB';

And to see how full the log actually is, rather than how big the file is:

USE AppDB;
GO
SELECT total_log_size_in_bytes / 1048576.0 AS log_file_mb,
       used_log_space_in_bytes  / 1048576.0 AS used_mb,
       used_log_space_in_percent           AS used_pct
FROM   sys.dm_db_log_space_usage;

A 200 GB log file that is 3% used is not a truncation problem. It is a file that was allowed to grow once and never came back down, which is the one case where shrinking is legitimate. A 20 GB log file that is 99% used is a truncation problem, and shrinking it would achieve nothing at all.

What log_reuse_wait_desc is telling you

For ACTIVE_TRANSACTION, find the culprit:

DBCC OPENTRAN('AppDB');

-- or, with more detail:
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       t.database_transaction_begin_time,
       t.database_transaction_log_bytes_used / 1048576.0 AS log_used_mb
FROM   sys.dm_tran_database_transactions AS t
JOIN   sys.dm_tran_session_transactions  AS st ON st.transaction_id = t.transaction_id
JOIN   sys.dm_exec_sessions              AS s  ON s.session_id = st.session_id
WHERE  t.database_id = DB_ID('AppDB')
ORDER  BY t.database_transaction_begin_time;

A transaction opened three days ago by an application that has since been restarted will hold the entire log hostage, and no amount of shrinking will release it. The related failure mode in tempdb is covered in The Real Fix for an Orphaned Transaction That Fills TempDB.

Why routine shrinking is a bad habit

Shrinking a log is not neutral, and the damage is not the same as shrinking a data file. There are three distinct costs.

1. It grows straight back

The log is as big as it is because at some point the workload needed it that big. Index maintenance, a monthly batch job, a large delete. Shrink it and the next run of that same workload grows it again, so you have spent I/O to achieve nothing that lasts past Tuesday.

2. Log growth cannot use instant file initialization

Data files can be created and grown instantly when the service account holds the Perform Volume Maintenance Tasks right. Log files never can. Every byte of new log space is zero-filled first, always. That means a growth event on a busy system is slow, and transactions that need log space wait for it to finish. Repeatedly shrinking a log is therefore repeatedly scheduling a stall for later.

3. It shreds the log into small VLFs

Every growth event adds a set of VLFs. Shrink and grow in small steps often enough and you end up with thousands of tiny ones. That slows down crash recovery, database startup, log backups and restores, and availability group synchronization. Count them:

SELECT COUNT(*) AS vlf_count
FROM   sys.dm_db_log_info(DB_ID('AppDB'));

A few dozen to a few hundred is normal. Several thousand means somebody has been shrinking, or autogrowth is set to a tiny increment, or both. On anything older than SQL Server 2016 SP2 the DMV does not exist and you are back to counting rows from DBCC LOGINFO.

When shrinking is genuinely correct

There is a real case for it: something exceptional and non-recurring inflated the log far past its normal working size. A one-off bulk load, an index rebuild on the largest table in the database, a runaway transaction that has since been resolved. The log is now 300 GB, the workload has never needed more than 20 GB, and that space is worth reclaiming.

The right sequence is truncate, then shrink, then grow back once to the size you actually want:

-- 1. Make the space reusable (FULL/BULK_LOGGED only; SIMPLE does this itself)
BACKUP LOG AppDB TO DISK = 'D:\Backup\AppDB_log.trn';

-- 2. Shrink the file down
USE AppDB;
GO
DBCC SHRINKFILE (AppDB_log, 1024);   -- target in MB, deliberately small

-- 3. Grow it back to the right size in ONE step, so you get few, large VLFs
ALTER DATABASE AppDB
  MODIFY FILE (NAME = AppDB_log, SIZE = 20480MB);

-- 4. Sane autogrowth as a safety net, not as a sizing strategy
ALTER DATABASE AppDB
  MODIFY FILE (NAME = AppDB_log, FILEGROWTH = 1024MB);

Step 3 is the one people skip, and it is the one that matters. Shrinking and then letting autogrowth crawl the file back up in small increments is how you get the VLF problem described above. Decide the size, set it once, and leave it alone.

Percentage-based autogrowth is a trap for the same reason: 10% of a small log is a pointless increment, and 10% of a large log is a very long zero-fill at the worst possible moment. Use a fixed number of megabytes.

The short version, one more time

If you remember one thing: when the log fills up, the question is never "how do I make this file smaller." It is "what is stopping SQL Server from reusing the space it already has," and log_reuse_wait_desc answers that in one query.

Questions people actually ask