|
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
(comments are locked)
|
|
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? 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!
Jul 14 '10 at 07:56 AM
WilliamD
(comments are locked)
|
|
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 :-)
Jul 14 '10 at 11:43 PM
WilliamD
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.
Jul 15 '10 at 05:43 AM
ThomasRushton ♦
(comments are locked)
|
