x

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
more ▼

asked Jul 14 '10 at 07:38 AM in Default

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Jul 14 '10 at 07:49 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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)
10|1200 characters needed characters left

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"
more ▼

answered Jul 14 '10 at 07:47 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Jul 14 '10 at 09:08 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x186
x63
x5

asked: Jul 14 '10 at 07:38 AM

Seen: 2187 times

Last Updated: Jul 14 '10 at 07:38 AM