SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Collection of Good Information About Controlling VLFs in SQL Server

Because of the default settings in SQL Server (for the model database), and a lack of understanding about how the transaction log should be sized and maintained, I run into many databases that have a high number of virtual log files inside the transaction log file.

Even though I have written about this before, a recent incident has prompted me to reiterate some of the great content that is available on this subject.

Transaction Log VLFs – too many or too few?

8 Steps to better Transaction Log Throughput

Inside the Storage Engine: More on the circular nature of the log

Performance impact: a large number of virtual log files – Part I

Slow recovery times and slow performance due to large numbers of Virtual Log Files

How a log file structure can affect database recovery time

Detecting and Reducing VLFs in SQL Server 2008/2008 R2 Transaction Log Files

The gist of all of this is that you should check the number of VLFs on each of your databases, and if they are over about 50, take the easy steps needed to reduce them. Then, based on your business requirements and log generation rate, manually grow your log file to an appropriate size to avoid having auto-grow kick in during normal operation (including index maintenance). I always leave auto-grow enabled as a safety measure, but with the growth increment set to an explicit size in MB.


No comments.

Leave a Comment

Please register or log in to leave a comment.