question

WilliamD avatar image
WilliamD asked

Why is the VLF count ballooning on me

Hi all, I have a database in full recovery mode, with a log backup every 15 minutes. This is set to 8GB starting size and I allow autogrowth of 8GB steps in case the log file fills up. The initial 8GB is fine, just sometimes when I do index maintenance it will jump to 24 GB (full index rebuild everywhere regardless of fragmentation - once a month). As a rule, I keep the logfile at 8GB due to size restrictions on our sandbox system that restores the DB each night (I will fix this, but I still want to know why things are behaving strangely). When autogrowth kicks in I am seeing astronomical VLF creation (looking today I had 150000+ VLFs). According to [Kim Tripp's explanation][1] I should be seeing 16 VLFs for the 8GB autogrowth expansion. Does anyone know what could be causing the autogrowth to be working differently for my system? Many thanks William [1]: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
sql-server-2008backuptransaction-logvlf
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
Yes - you could well be hitting the issue noted at the bottom of Kimberly's article - [log file growth broken for multiples of 4GB][1]. If you are hitting that issue, then it will be adding tiny itty bitty bits to the log, each one with another 4 VLFs. It does say in that article that the growth works the second time round, but I wouldn't be surprised if it was possible that it could fail repeatedly... So - maybe worth setting it to 8.001 GB autogrowth, or just keeping a bigger log file around permanently? [1]: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt, I read Kim's bug as being for exactly 4GB and not multiples of it. I have changed it to 8193MB growth to break the multiplier. I'll see if it "fixes" the problem the next time we raise the roof. Thanks to you and Oleg!
0 Likes 0 ·
Oleg avatar image
Oleg answered
On the bottom of her explanation, Kim placed the warning sign: "**BEWARE** - there's a bug when growing the log in multiples of 4GB. Check out Paul's post for more information: [Bug: log file growth broken for multiples of 4GB][1]. The bug described there is not really consistent with the problem you describe, but I think that it might be worth it to send Paul Randal a private message through SSC site and ask him to check it out. [1]: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx"
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Is the database being restored to a new, clean database? Or over the top of the existing one? I just wonder if that, coupled with an autoshrink process, may also impact on the number of VLFs. To those more experienced than me - what do you reckon? Am I barking up the wrong tree?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Thomas, I am restoring to a new clean database, and don't have autoshrink enabled here. The problem was observed on the production system, I didn't think to check the sandbox, as it is of much less interest. As the problem is now "fixed", I don't know if I will see it occur again. I don't have the time to replicate the behaviour at the moment, maybe in a century or so :-)
0 Likes 0 ·
Hi William - thanks for the update. It was just a thought... I know the feeling about not having time to investigate these things - that's why I asked the question - someone else may know the answer. Glad you've got it sorted.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.