DB-유

Shrinking the SQL Server Transaction Log: When It Makes Sense

· mssql, storage, maintenance

"The log file is 200 GB and the disk is full" is the phone call I dread most, and I've taken it more often than I'd like. The reflex is to shrink the file. Sometimes that's right, but shrinking without knowing why the log grew just guarantees a repeat. Below is how I find the reason, when a shrink is the right fix, and how to do it without leaving thousands of virtual log files behind.

Up front: a scheduled shrink job is a mistake. The log grows back to what the workload needs, and each growth is a synchronous, zero-initialised write that stalls transactions.

First, why isn't the log being reused?

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

The log_reuse_wait_desc column tells you what is holding the log:

  • NOTHING: the log can be reused; the file is just oversized from a past event.
  • LOG_BACKUP: full recovery model but no log backups are running. This is the usual culprit.
  • ACTIVE_TRANSACTION: a long-running or open transaction. Find it with DBCC OPENTRAN('ShopDB').
  • REPLICATION or AVAILABILITY_REPLICA: a subscriber or secondary is behind.
  • CHECKPOINT: simple recovery model, waiting for the next checkpoint.

Also check actual usage:

DBCC SQLPERF(LOGSPACE);

If Log Space Used (%) is low and the wait is NOTHING, a shrink will succeed. If usage is high, fix the cause first.

Fix the cause

For LOG_BACKUP, either start taking log backups or, if point-in-time recovery is genuinely not required (a dev box, a staging copy), switch to simple recovery:

ALTER DATABASE ShopDB SET RECOVERY SIMPLE;

For ACTIVE_TRANSACTION, DBCC OPENTRAN gives you the SPID; look at what it is doing with DBCC INPUTBUFFER(spid) before killing it. I check first because I once killed something that turned out to be a finance month-end job. A rollback of a huge transaction can take as long as the transaction itself.

If you are in full recovery and just need to free space right now, take a log backup first so the active portion moves:

BACKUP LOG ShopDB TO DISK = N'E:\Backup\ShopDB_log_emergency.trn' WITH COMPRESSION;

Look at the VLF layout

The log is divided into virtual log files. Shrink can only remove space from the end of the file, back to the last active VLF.

DBCC LOGINFO('ShopDB');

Each row is a VLF; Status = 2 means active. If the active VLFs are at the end of the file, a log backup (or checkpoint in simple mode) and a small amount of activity will usually wrap the log back to the beginning. Sometimes you need two log backups in a row.

Count the rows. More than a few hundred VLFs slows recovery and replication; more than a thousand is a problem worth fixing with the shrink-and-regrow approach below.

The shrink itself

Get the logical file name, then shrink to a target in MB:

SELECT name, size / 128 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';

DBCC SHRINKFILE (N'ShopDB_log', 1024);

Do not use DBCC SHRINKDATABASE; it also shrinks the data files and fragments every index in the process. SHRINKFILE on the log file has no fragmentation impact on data, because the log is not an index structure.

Regrow it on purpose

After shrinking to a small size, grow the log back to what the workload needs, in chunks of up to 8000 MB so each growth creates 16 VLFs of a reasonable size:

ALTER DATABASE ShopDB MODIFY FILE (NAME = N'ShopDB_log', SIZE = 8000MB);
ALTER DATABASE ShopDB MODIFY FILE (NAME = N'ShopDB_log', SIZE = 16000MB);
ALTER DATABASE ShopDB MODIFY FILE (NAME = N'ShopDB_log', FILEGROWTH = 1024MB);

Set FILEGROWTH to a fixed size in MB, never a percentage. Percentage growth on a large log produces huge unpredictable growth events.

Run DBCC SQLPERF(LOGSPACE) and DBCC LOGINFO again. You want the new size, a low used percentage, and a VLF count in the tens rather than thousands. Then watch log_reuse_wait_desc for a day; if it sits at NOTHING or briefly at LOG_BACKUP between backups, the cause is fixed.

Gotchas

"Cannot shrink log file 2 (ShopDB_log) because the logical log file located at the end of the file is in use." The active VLF is at the tail. Take a log backup, run a few small transactions, and try again. In simple recovery, run CHECKPOINT first.

"Cannot shrink log file 2 (ShopDB_log) because total number of logical log files cannot be fewer than 2." You asked for a target smaller than two VLFs allow. Pick a slightly larger target.

Shrink runs but the file does not get smaller. The target you passed is smaller than the currently allocated active portion; check DBCC SQLPERF(LOGSPACE) for used space. You cannot shrink below what is in use.

Log fills again within days. You did not fix the cause. Almost always this is a missing log backup job or an index rebuild job on a very large table in full recovery model. Index rebuilds are fully logged; for that maintenance window either accept the log size or switch to bulk-logged recovery temporarily.

My rule: one shrink per incident, after a one-off event like a bulk load or a dead backup job. Then leave the log alone.