January 23, 2009 at 10:20 am
One last thing that I should note is the link to the article I keep referring to. The article describes pre-production tasks and can be found here:
http://qa.sqlservercentral.com/articles/Administration/64732/. I implemented almost every step the author outlines, including the indexing and update statistics steps.
January 23, 2009 at 10:23 am
with the sizes you quote I would definitely split up the reindexing job, one for the large table, one for the others. As an estimate a 50Gb log as a starting point might not be unreasonble for you, espec if you use rebuild.
unless you know auto update stats is causing problems i would turn it on, the default is set to on for a reason , its usually the best setting.
Can't quite see the point of reorganising if not much fragmentation, there won't be much for reorganise to do. Best to decide which method is best for you, so you need to test it. Run the rebuild, see how long it takes, any blocking, how good the results are. you could always do a comparison reorganise run and compare results. Once you know which works best, stick with that.
If users are not sure about recovery, follow your instincts and set recovery mode to full.
---------------------------------------------------------------------
January 23, 2009 at 10:33 am
Cathy DePaolo (1/23/2009)
One last thing that I should note is the link to the article I keep referring to. The article describes pre-production tasks and can be found here:http://qa.sqlservercentral.com/articles/Administration/64732/. I implemented almost every step the author outlines, including the indexing and update statistics steps.
I don't think ken is advocating turning off auto update stats, more do manual updates as well. Stats will be updated when you do a rebuild,but not when you reorganise. for your large table if it is updated heavily but not enough to trigger an auto update a daily manual update out of hours may well help.
---------------------------------------------------------------------
April 13, 2012 at 3:48 am
i think instead of switching recovery mode, you could simply set up another job that will shrink you database once you take the transaction log backup after the index rebuilding.
say for example you rebuilding index on Saturday 5AM. you take transaction log backup at 6AM. set up a shrink job at 6:10 AM as that time your actual transaction Log size will be something 99% empty. so if you shrink and set 10% free space, that would be enough space for you.
April 13, 2012 at 3:59 am
dba.zahid (4/13/2012)
i think instead of switching recovery mode, you could simply set up another job that will shrink you database once you take the transaction log backup after the index rebuilding.say for example you rebuilding index on Saturday 5AM. you take transaction log backup at 6AM. set up a shrink job at 6:10 AM as that time your actual transaction Log size will be something 99% empty. so if you shrink and set 10% free space, that would be enough space for you.
oh, no, no, no. dont shrink your log, it will just have to grow again after the next reindex. Thats a waste of resources.
---------------------------------------------------------------------
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply