What’s the issue?
SQL Server database has one primary transaction log file by design, and SQL Server writes to the log sequentially regardless of how many log files exist. Despite this, some databases are configured with multiple transaction log files, often added in response to a one-time emergency such as the original log volume running out of space, with the intent to remove the extra file later once the situation was resolved.
Why is this a problem?
Multiple transaction log files provide NO performance benefit. Unlike data files, which can be spread across multiple files in a filegroup to parallelize I/O, the transaction log is written sequentially, so adding more log files does not increase throughput. The extra files simply add complexity: each one must be backed up, restored, and managed alongside the primary log, and they consume storage on whatever volumes they reside on.
Forgotten secondary log files often live on volumes that were never intended to be permanent log storage, creating dependencies that surprise teams during disaster recovery or migrations. They can also obscure the real cause of past log growth issues, since the existence of multiple log files suggests a problem was worked around rather than solved.
What should you do about this?
Identify affected databases by querying sys.master_files where type_desc = ‘LOG’ and grouping by database_id to find any database with more than one log file. For each one, plan to remove the extra log files and consolidate to a single, properly sized log file on the appropriate volume. To remove a secondary log file, first ensure it is empty by running DBCC SHRINKFILE (N’LogicalFileName’, EMPTYFILE);, which may require a transaction log backup or a checkpoint to fully drain it. Once the file is empty, remove it with ALTER DATABASE [DatabaseName] REMOVE FILE [LogicalFileName];.
After consolidation, confirm the remaining log file is sized appropriately for the workload and configured with sensible autogrowth settings to prevent future emergency additions. Investigate the original cause of the runaway log growth that prompted the secondary file in the first place (missing log backups, long running transactions, replication issues), and address it so the situation does not recur.