Overview
Multiple transaction log files absolutely does NOT help performance. Multiple data files, however, does help so I think people believe since multiple data files can be beneficial that multiple log files are. Not true.
Explanation
The transaction log is sequential, so SQL Server doesn’t perform parallel I/O’s to the transaction log if there are multiples. If your system does have multiple log files, the first file is used in its entirety, then the second file will be used, and so on.
The only time a second transaction log file might be needed is if the first transaction log is full and the transaction log won’t clear, it will then flip over to the second log file, allowing database modifications until the first log file can clear.
Brady has been in the IT industry for 10+ years. He has worked in administrative roles using MSSQL 2000 to 2012 as well as Sharepoint 2007 and 2010. He currently serves as a Database Administrator in Nashville, TN. You can view his blog @ http://www.sqlbrady.com.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
