Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

MCM Prep - VLF Allocations

I had heard that you could have too many VLFs in your transaction log, and it could cause performance issues. Kimberly Tripp has a great blog on how to get better performance from your log, but until I was studying for the MCM, I didn’t realize you could have too few.

The issue seems to be that if you allocate too much transaction log at once, you could end up with a few, very, very large (GB in size) VLFs, and despite regular log backups, you could not be clearing any VLFs inside the log until there are no active transactions in that VLF. And that could cause a performance slowdown as it clears that large VLF.

How are VLFs allocated? Here’s the formula:

  • chunks less than 64MB = 4 VLFs
  • chunks of 64MB and less than 1GB = 8 VLFs
  • chunks of 1GB and larger = 16 VLFs

This is from : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx#ixzz19KysnYcK

So if you need 50GB of log space, do as Kim recommends and get 512MB chunks by allocating 8GB at a time. You can do this contiguously, as in all the same day, but allocate 8GB, let it finish, then ALTER to add 8GB more, repeat until you reach the correct size.

Comments

Posted by Glenn Berry on 30 December 2010

I think Paul and Kimberly have an update where they advocate growing the log file in an 8000MB increment (rather than 8GB) since there is an issue when you grow it in exact 4GB multiples.

I have had very good luck at NewsGator growing my log files in roughly 8000MB increments to get to my desired size (which is based on my log generation rate and log backup frequency).

Just be aware that growing your log file in a relatively large chunk will take a bit since the allocation has to be zeroed out (Windows Instant File Initialization does not work on log files).

Posted by NULLgarity on 30 December 2010

Due to a bug described here (www.sqlskills.com/.../Bug-log-file-growth-broken-for-multiples-of-4GB.aspx), the recommendation is now 8,000 MB rather than 8 GB.

Posted by Jason Brimhall on 30 December 2010

Nice to have reminders like this.  Thanks Steve.

Leave a Comment

Please register or log in to leave a comment.