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] 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 :
Yes - you could well be hitting the issue noted at the bottom of Kimberly's article - [log file growth broken for multiples of 4GB]. 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? :
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]. 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. :
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?