Do multiple transaction logs help performance?


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.

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.

Comments For This Article

get free sql tips
agree to terms