SQL Server Transaction Log files - performance myth

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

Transaction log 

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

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server Transaction Log files - performance myth

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer