13 June,2011 by Jack Vamvas
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
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