Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
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.

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


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

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


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