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?
Best Answer
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.