Quick Scan Report – Log Files Larger Than Database

What this check looks for

Databases where the total size of the log files is substantially larger than the total size of the data files, from sys.master_files.

Why it matters

A log file does not grow because of how much data you have. It grows because it could not be truncated while transactions were being written.

So an oversized log is a historical record. It is the high water mark of some period when truncation was blocked, and the size tells you roughly how long that period was. The database being 4 GB with a 60 GB log says something happened, and probably still is.

The current cause is in one column, log_reuse_wait_desc:

NOTHING is an important answer, not an absence of one. It means the cause has gone and the file simply never shrank, because SQL Server does not reclaim log space on its own.

What the size actually costs you:

• Disk, which is the obvious one and often the reason the finding was noticed.
• Startup and recovery time, because recovery walks the log’s virtual log files, and a log that grew in small increments has an enormous number of them. That is the VLF check, and this finding and that one nearly always appear together.
• Restore time, for the same reason, at the worst moment.
• Log backup time.

And free space inside the log is not waste. A log that reached 20 GB because a nightly index rebuild needs 20 GB of log space needs to stay at 20 GB. Shrinking it means it grows back every night, and each regrow adds VLFs. The question is not “is the log large” but “is the log larger than it needs to be”.

How to confirm it yourself

The comparison, with the reason alongside:

SELECT DB_NAME(mf.[database_id])                                                AS [database_name],
       CAST(SUM(CASE WHEN mf.[type] = 0 THEN mf.[size] END) * 8.0 / 1024 AS DECIMAL(12,1)) AS [data_mb],
       CAST(SUM(CASE WHEN mf.[type] = 1 THEN mf.[size] END) * 8.0 / 1024 AS DECIMAL(12,1)) AS [log_mb],
       CAST(SUM(CASE WHEN mf.[type] = 1 THEN mf.[size] END) * 1.0
            / NULLIF(SUM(CASE WHEN mf.[type] = 0 THEN mf.[size] END), 0) AS DECIMAL(6,2))  AS [log_to_data_ratio],
       d.[recovery_model_desc],
       d.[log_reuse_wait_desc]
  FROM sys.master_files AS mf WITH (NOLOCK)
 INNER JOIN sys.databases AS d WITH (NOLOCK) ON d.[database_id] = mf.[database_id]
 WHERE mf.[database_id] > 4
 GROUP BY mf.[database_id], d.[recovery_model_desc], d.[log_reuse_wait_desc]
 ORDER BY [log_to_data_ratio] DESC;

How much of the log is actually in use, which is the number that decides whether shrinking is safe:

SELECT DB_NAME([database_id])                                        AS [database_name],
       CAST([total_log_size_in_bytes] / 1048576.0 AS DECIMAL(12,1))  AS [log_size_mb],
       CAST([used_log_space_in_bytes] / 1048576.0 AS DECIMAL(12,1))  AS [used_mb],
       CAST([used_log_space_in_percent] AS DECIMAL(5,1))             AS [used_pct]
  FROM sys.dm_db_log_space_usage;

That one is per database, so run it in the database concerned.

And the VLF count, which usually accompanies it:

SELECT COUNT(*) AS [vlf_count] FROM sys.dm_db_log_info(DB_ID());

How to fix it

Fix the cause first. Shrinking before the cause is fixed gives you the same log again, plus more VLFs.

If log_reuse_wait_desc is LOG_BACKUP: start taking log backups, or switch the database to simple recovery if it does not need point in time recovery. Both are covered by their own check.

If it is ACTIVE_TRANSACTION: find the session. The long open transaction check covers it.

If it is AVAILABILITY_REPLICA or REPLICATION: the secondary or the agent is the problem, and both have their own checks.

If it is NOTHING: the cause has gone, and the log is simply the size it grew to. This is the case where shrinking is legitimate.

Then, once the cause is dealt with, resize it once and properly:

-- 1. back up the log so the space becomes reusable
BACKUP LOG [YourDatabase] TO DISK = N'\\backupserver\sqlbackups\YourDatabase_log.trn'
  WITH CHECKSUM, COMPRESSION;

-- 2. shrink right down
USE [YourDatabase];
GO
DBCC SHRINKFILE (N'YourDatabase_log', 1);

-- 3. grow back in a few large steps, to the size it genuinely needs
ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', SIZE = 8GB);

-- 4. and a sensible growth increment
ALTER DATABASE [YourDatabase] MODIFY FILE (NAME = N'YourDatabase_log', FILEGROWTH = 512MB);

Step 3 is the one people skip, and it is the important one. Shrinking and leaving it small means it grows back under load, in small increments, generating thousands of VLFs. Growing it deliberately in a few large steps gives you the same size with a healthy VLF count.

What size? Big enough for the largest thing it has to hold: the longest transaction, the biggest index rebuild, and whatever accumulates between log backups. If the log reached 20 GB doing normal work, 20 GB is the right size.

Do not schedule shrinking. A recurring shrink is how this becomes permanent, and it has its own checks in the autoshrink and maintenance plan findings.

How long it takes

About two hours per database, most of it the shrink and regrow, and it needs no downtime.

Related reports

Related checks

Frequently asked questions

Is a large log always wrong? No. A log sized for the largest index rebuild is correct and should stay that way. The finding is a prompt to check whether the size is needed or historical.

log_reuse_wait_desc says NOTHING. Can I shrink? Yes, this is the case where it is appropriate. Shrink once, then grow back deliberately to the size it needs.

Why grow it back after shrinking? So it does not grow back on its own, in small increments, under load. Growing it deliberately in a few large steps gives the same size with far fewer VLFs.

The log is 10 times the data file on a small database. On a very small database the ratio is easy to trip. Look at the absolute size and at log_reuse_wait_desc rather than the ratio alone.