Multiple Log Files on the Same Drive
Blitz Result: Multiple Log Files on the Same Drive
By default, SQL Server starts each database with one transaction log file. The transaction file is used sequentially, not serially, and there’s no performance benefit to having multiple log files. SQL Server just starts working at the beginning of the first log file, works through to the end of it, and then starts with the next log file.
In theory, then, you should never have two log files. In practice, sometimes this is necessary if you’re working with a limited amount of drive space in an array dedicated to the transaction log. If the transaction log grows out of control and fills up its dedicated array, you may have to add an additional file on another array so that the database doesn’t grind to a halt. That’s okay (not great, but okay).
However, there’s not a scenario where multiple log files on the same drive are beneficial. This part of our SQL Server sp_Blitz® script checks sys.database_files looking for one database with multiple log files on the same drive.
To Fix the Problem
Cut back down to just one log file per database per drive. For example, if your Sales database has two transaction logs on the F: drive, remove one of them. Unfortunately, you can’t remove it if it’s in active use – to learn more about that, check out Tibor Karaszi’s post on removing transaction log files.
After this change, you probably won’t see a performance improvement – but you might if you were having the problem of too many virtual log files inside the log file you deleted.