SQL Server Transaction Log files - performance myth

Adding an extra SQL server  transaction log  file will not improve throughput performance.

SQL Server  Transaction Log files are written  as sequential  for a database.

SQL Server uses one transaction log file at a time – no matter how many log files are assigned to a database.

 I have seen SQL Server databases configured  as below, in the mistaken belief , both SQL Server transaction log files are used simultaneously. The actual purpose of the configuration below is to manage multiple transaction log files , usually in different physical locations

To improve SQL server  transaction log file performance:

1)Predict long running queries , that cause the log file to fill up. Create extra log files to support the long running query.

2)Separate data and log files onto separate disks

3)Avoid auto-growth. Size the transaction log file for the maximum size-this avoids multiple virtual log files – which causes a slow database start up. Read more on Database has more than 1000 virtual log files which is excessive – informational message 9017

4)Faster disks – accompanied by an overall analysis of the performance . Look at async_io_completion

Read More on Managing Transaction Logs

The transaction log for database is full due to ACTIVE_TRANSACTION

SQL Server - Preallocate SQL Transaction Logs for large queries ...

Manage Transaction Logs during Index Rebuild - SQL Server DBA