Reducing high VLF count

Question

I have been using Brent Ozar's sp_blitz script to review our database setup and have found that the VLF count is high (13562!) on the primary DB since changing to Full recovery model.

I ran a full index rebuild at the weekend during which the log file swelled to about 32GB on a 46GB data file with 10MB log autogrowth which I imagine has caused the high VLF count.

From what I understand I should have upped the autogrowth, however this is not typical activity. Typically the .trn files backed up every few hours are 100-200MB so 10MB doesn't seem ridiculous.

So, I think I need to shrink the log file and allow it to grow again in as few growths as possible as scripted here out of hours. Does this sound sensible in this particular situation?

Also, the initial size of the log file is now 33896MB should I reduce this to something more sensible (200MB) after the shrink?

Answer 1

10MB is ridiculous, IMHO. Is your I/O subsystem that slow where limiting growth to such a small increment is necessary? Ideally you should not be observing AutoGrow at all, by sizing the log large enough to handle your largest transactions anyway. And only use AutoGrow as a crutch to protect you from abnormal events, such as this one, in which case it should be rather large.

And no, I would not reduce your log file to 200MB, and I'm not sure how that is "more sensible." Growth events are expensive, particularly with the log file, since the expanded portion of the file has to be zeroed out. You want to size the log file in such a way that you will avoid any predictable growth events. If you will be performing another similar index rebuild in the future, I don't understand what you would gain by shrinking the log file to 200MB in the meantime. Are you going to lease the freed up space out to someone else, then kick them out when your log needs the space again? I would say leave the log at 32GB (shrinking it and expanding it one event, as the script you pointed out suggested) and monitor for AutoGrow events (these are captured in the default trace, so you don't have to set anything up, just look at it once in a while). (On more modern versions, should think about using Extended Events for tracking growth events instead.) This will tell you if you need to consider making it even bigger.

There is no prize for smallest log file - you want it to be large enough to accommodate all of your normal (and abnormal) log activity without having to grow.

Answer 2

13562 is quite a high VLF number! The restore (have you tried one recently ?) will surely take a very long time, at least the first few percents. There was a post of Brent Ozar on this subject, but I can not find it yet...

The first question I ask myselft is what are my maintenance plans doing that can have an impact on this log file ? Log backups, index rebuild/reorganize, ... As far as the index are concerned, are you able to rebuild/reorganize them depending on their fragmentation? Please have a look at Ola Hallengreen Index maintenance for the cure or sp_blitzindex by Ozar for the diagnosis. This can ease the task for SQL Server as huge rebuilds are rather costly. So how often do you do index maintenance?

As far as the sizes are concerned, I'd leave the log initial size to close to its actual size. For the autogrowth parameter, please put something much bigger to reduce those cost-intensive operations.