How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203

Question: How can you prepare a log-file shrink command for SQL Server databases?

Answer: Use DBCC SHRINKFILE for an individually reviewed log file. Generate commands for inspection first, and shrink only when there is a real one-time reason to reclaim excess allocated space.

Shrinking can hurt performance, and that warning matters more than a convenient script that shrinks every log on an instance. So this query only writes the commands for you to review:

DECLARE @Database sysname = NULL; -- set one database name, or NULL to inspect all
DECLARE @TargetMB int = 1024; -- example only; choose from measured peak requirements
IF @TargetMB < 1 THROW 50001, 'Target size must be positive.', 1;
SELECT d.name AS DatabaseName, f.name AS LogicalLogName,
       f.size / 128.0 AS CurrentSizeMB,
       d.recovery_model_desc, d.log_reuse_wait_desc,
       N'USE ' + QUOTENAME(d.name) + N'; DBCC SHRINKFILE ('
       + CONVERT(nvarchar(11), f.file_id) + N', '
       + CONVERT(nvarchar(11), @TargetMB) + N');' AS CommandToReview
FROM sys.master_files AS f
JOIN sys.databases AS d ON d.database_id = f.database_id
WHERE f.type = 1 AND d.database_id > 4 AND d.state_desc = N'ONLINE'
  AND (@Database IS NULL OR d.name = @Database)
ORDER BY d.name, f.file_id;
-- This query returns commands only. It never executes them.

It returns one command per log file instead of executing a concatenated batch. File IDs avoid awkward logical-name quoting; QUOTENAME protects the database name. Set a target from the workload’s actual requirements. The 1024 MB value is an example, not a recommendation for every database.

Log truncation makes inactive log space reusable inside the file; shrinking returns eligible space at its end to the operating system. They are not the same operation. Check log_reuse_wait_desc. In FULL recovery, CHECKPOINT alone doesn’t replace the log backups needed for reuse, and an active transaction or another dependency can still prevent truncation.

An oversized log can be worth reviewing before a big backup, because a restore recreates the full allocated log size. That’s an exceptional reason to look at its size, not a reason to shrink before every backup. A log sized for normal peak activity will usually need that space again, and repeated shrink-and-grow cycles add avoidable work.

Keep a verified recovery path, resolve the reason the log grew, and review the file on its own before copying any generated command into a maintenance session. If you have a better diagnostic script, share it and I will credit you.

A log shrink is not maintenance, it is a one-time repair, so find why the log grew before you run it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.