http://www.sqlservercentral.com/blogs/steve_jones/2010/12/30/mcm-prep-_2D00_-vlf-allocations/

Printed 2014/07/27 10:51PM

MCM Prep - VLF Allocations

By Steve Jones, 2010/12/30

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:

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.