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.

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

Republished from http://www.sqlserver-dba.com.