What’s the issue?
A SQL Server database has one transaction log file by design, and SQL Server writes to the log sequentially regardless of how many log files exist. Tempdb is no exception to this rule and operates with a single log file under standard configuration.This finding identifies instances where tempdb has been configured with more than one transaction log file. The configuration is often the result of a one-time emergency response, where a second log file was added to handle a tempdb log space issue and was never removed afterward, although sometimes it is due to a misunderstanding of how the transaction log functions.
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 reduce allocation contention, the transaction log is written sequentially, so adding more log files does not increase throughput or reduce latency. The extra files simply add complexity with no offsetting benefit.For tempdb specifically, multiple log files can complicate operations and management. Tempdb files are recreated at every service restart, and any non-standard configuration must be maintained correctly across restarts. Forgotten secondary log files can also live on volumes that were never intended for permanent tempdb storage, creating storage dependencies that are not obvious to the team and that complicate disaster recovery and migrations.
The presence of multiple tempdb log files often indicates that a past tempdb log issue was worked around rather than fully resolved. The original problem (a long-running transaction, a runaway version store, or a workload that produced unexpected tempdb log growth) may still be present, only masked by the additional log space. Reviewing the multiple-log-file condition is an opportunity to identify and address the root cause.
What should you do about this?
Identify the tempdb log files by querying sys.master_files for files where database_id = 2 and type_desc = ‘LOG’. Capture the logical and physical file names, sizes, and locations of all log files, and identify which file is the primary and which are extra.Plan to remove the extra log files during a maintenance window. Use ALTER DATABASE tempdb REMOVE FILE [LogicalFileName]; to mark each extra log file for removal. The removal takes effect at the next SQL Server service restart, since tempdb files cannot be removed while they are in use. After the service restart, verify that tempdb has only one log file remaining and that the file is sized appropriately for the workload.
Investigate the original cause of the runaway log growth that prompted the secondary file in the first place. Common causes include long-running transactions, missing transaction log management for snapshot isolation activity, or workload patterns that produce unusually large tempdb log activity.