September 14, 2009 at 9:24 am
bpowers (9/14/2009)
Ok. The weekly maintenance job executed last night without a hitch. However, due to the rebuild on the indexes the log file grew 4G. The VLF's grew from 4 to over 600 on the db. Is this normal? Should I be worried about the growth? I notice that the rebuild index option on the maintenance task does not have the option to set the rebuild at a certain fragmentation rate. How does it determine when to rebuild an index? Any help will be appreciated.
If a rebuild is issued inside the maintenance plan, the index will be rebuilt. If you want the rebuild to be conditional upon index fragmentation, you'll need to run a script which walks down the indexes, checks for fragmentation, then issues a rebuild depending on what it finds. This functionality isn't built in to SSMS.
The growth of the log file is normal, assuming you aren't setting the database to BULK_LOGGED before running the index rebuilds. The number of VLFs is the problem you'll want to address; 600 is much higher than you'd like to see (by an order of magnitude). This is a result of letting autogrow handle your log file size.
Given that you still don't really know how big the log file needs to be, your best bet is to let it grow until it plateaus. Then, during one of your maintenance windows, manually shrink the log file as much as possible (backup the log, then run a DBCC SHRINKFILE against it), then manually set its size to a bit more than where it plateaued.
______
Twitter: @Control_Group
September 14, 2009 at 9:36 am
I can do that. Thanks for the feedback.
September 14, 2009 at 4:49 pm
That was steps 4 and 5 in the plan I posted before, so I couldn't agree with Matt Cherwin more.
A nice explanation.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply