Quick Scan Report – Multiple Log Files

What this check looks for

Databases with more than one file of type LOG in sys.master_files. The message names the database.

Why it matters

SQL Server writes the transaction log sequentially, to one file at a time. A second log file does not give you parallel log writes, because there is no such thing.

That is the whole argument, and it is worth stating plainly because the intuition from data files is exactly wrong. Multiple data files genuinely help: allocations are spread across them proportionally, which reduces allocation contention and can spread I/O across spindles. Multiple log files do none of that. SQL Server fills the first log file, then moves to the second, then wraps back around. Only one is being written at any moment.

So what a second log file actually gives you:

• No throughput improvement whatsoever.
• One more file to size, monitor, back up around and account for.
• Log growth scattered across volumes, so free space on two drives is consumed rather than one, and disk space monitoring becomes harder to reason about.
• Slower recovery, marginally, since recovery walks the virtual log files across both files.
• A restore that requires both file locations, which is one more thing to get right during a recovery.

How they appear is almost always the same story: the log volume filled at two in the morning, and adding a second log file on another drive was the fastest way to get the database working again. That is a correct emergency response. The finding is that the emergency ended and the file stayed.

A less common origin is somebody applying the multiple data file guidance to log files, which is the misunderstanding described above.

There is one legitimate case, and it is exactly the emergency one: temporarily adding a log file on another volume to get out of a full log situation while you fix the cause. The word is temporarily.

Removing it is usually easy and occasionally not, which is the practical content of this finding. A log file can only be removed when no active virtual log files live in it, and getting to that state is a matter of log backups and patience rather than force.

How to confirm it yourself

The files:

SELECT DB_NAME(mf.[database_id])                     AS [database_name],
       mf.[file_id],
       mf.[name]                                     AS [logical_name],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       CASE WHEN mf.[is_percent_growth] = 1 THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       mf.[physical_name],
       mf.[state_desc]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[type] = 1
   AND mf.[database_id] IN (SELECT [database_id] FROM sys.master_files
                             WHERE [type] = 1 GROUP BY [database_id] HAVING COUNT(*) > 1)
 ORDER BY [database_name], mf.[file_id];

Which file the active log is in, which decides whether you can remove one right now:

USE [YourDatabase];
GO
SELECT [file_id],
       COUNT(*)                                          AS [vlf_count],
       SUM(CASE WHEN [vlf_active] = 1 THEN 1 ELSE 0 END) AS [active_vlfs],
       SUM(CASE WHEN [vlf_status] = 2 THEN 1 ELSE 0 END) AS [in_use_vlfs],
       CAST(SUM([vlf_size_mb]) AS DECIMAL(12,1))         AS [total_mb]
  FROM sys.dm_db_log_info(DB_ID())
 GROUP BY [file_id];

The file you want to remove must show zero active and zero in-use VLFs. That is the whole gate on the removal.

On SQL Server 2016 and earlier, where that function is unavailable:

DBCC LOGINFO;

FileId identifies the file and Status of 2 means the VLF is in use.

Whether the log can truncate at all, which is the thing to fix first:

SELECT [name], [recovery_model_desc], [log_reuse_wait_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [name] = 'YourDatabase';

LOG_BACKUP means nothing will free up until you take a log backup, and the removal cannot proceed until it does.

How much of the log is used overall:

USE [YourDatabase];
GO
SELECT 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;

How to fix it

Empty the extra file, then remove it. Both are online; the only requirement is patience.

1. Make sure the log can truncate. If log_reuse_wait_desc says LOG_BACKUP, take a log backup. If it says ACTIVE_TRANSACTION, find and resolve the open transaction. Nothing else will work until the log can move on:

BACKUP LOG [YourDatabase]
    TO DISK = N'E:\SQLBackups\YourDatabase_log.trn' WITH CHECKSUM, COMPRESSION;

2. Empty the file you want to remove, which moves any remaining content into the other log file:

USE [YourDatabase];
GO
DBCC SHRINKFILE (N'YourDatabase_log2', EMPTYFILE);

3. Remove it:

ALTER DATABASE [YourDatabase] REMOVE FILE [YourDatabase_log2];

If the remove fails, the file still contains active VLFs. The remedy is to let the log cycle past them, which means log backups and, in simple recovery, checkpoints:

-- in full recovery: back up the log, then retry
BACKUP LOG [YourDatabase] TO DISK = N'E:\SQLBackups\YourDatabase_log.trn' WITH CHECKSUM;
DBCC SHRINKFILE (N'YourDatabase_log2', EMPTYFILE);
ALTER DATABASE [YourDatabase] REMOVE FILE [YourDatabase_log2];

-- in simple recovery: checkpoint, then retry
CHECKPOINT;
DBCC SHRINKFILE (N'YourDatabase_log2', EMPTYFILE);
ALTER DATABASE [YourDatabase] REMOVE FILE [YourDatabase_log2];

Repeat the cycle until it succeeds. On a busy database this can take several rounds, because the log has to wrap around past the VLFs in that file. That is normal and it is not a reason to force anything.

4. Then size the remaining log properly, which is the part that prevents a repeat:

ALTER DATABASE [YourDatabase]
  MODIFY FILE (NAME = N'YourDatabase_log', SIZE = 16GB, FILEGROWTH = 512MB);

Size it for the largest thing it has to hold: the longest transaction, the biggest index rebuild, and whatever accumulates between log backups. Growing it deliberately in one step also keeps the VLF count healthy.

5. And fix why it filled in the first place, since the second log file was a symptom. The usual causes each have their own check: full recovery with no log backups, a long open transaction, a replication or availability group secondary holding the log, an unbounded index maintenance job.

If you do need to add a log file in an emergency again, that is a legitimate response. Schedule its removal in the same change record, so it does not become next year’s finding.

How long it takes

About an hour, though it can take several cycles of log backups on a busy database. All of it is online.

Related reports

Related checks

Frequently asked questions

Would a second log file improve write throughput? No. The log is written sequentially to one file at a time, so only one is ever in use. This is the opposite of data files, where multiple files genuinely help.

Why will the file not remove? It still contains active virtual log files. Take a log backup, or checkpoint in simple recovery, then retry the empty and remove. On a busy database it can take several cycles.

Is it ever right to add a second log file? Yes, as a temporary emergency measure when the log volume has filled. Plan its removal at the same time you add it.

Does removing it need downtime? No. DBCC SHRINKFILE ... EMPTYFILE and ALTER DATABASE ... REMOVE FILE are both online.