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
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
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"
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?
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 :-)
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.
