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?
asked Jul 14, 2010 at 07:38 AM in Default
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?: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
answered Jul 14, 2010 at 07:49 AM
Matt Whitfield ♦♦
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.: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx"
answered Jul 14, 2010 at 07:47 AM
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?
answered Jul 14, 2010 at 09:08 AM