Maintenance Plan Job Steps

[rileypetty]

I have a question about setting up Maintenance Plan jobs and their configuration (SQL 2005). We’ve always run them as independent jobs, like one for integrity checks and one for reindexing and one for shrinking, etc. However, when I was doing the research looking for solutions to our integrity error problem I noticed a number of the DBA’s were running a single job with multiple steps, that certainly makes sense to me but I’m not sure about the sequence. Would you do an integrity check as the first step and a reindex or a rebuild as the second step and a shrink as the last step? Are there other steps I should be considering? Thanks for any advice you may be able to give me.

 

[DrSql]

Re-index and Rebuild is recommended based on fragmentation level and there are several scripts but I won’t suggest running shrink as part of it. If the db and log file grown to a size during the maintenance jobs then leave the size intact, constant file growth and shrink can cause disk fragmentation, also disk growth can delay transactions and performance.

Dr.Sql
Good Luck.

 

[rileypetty]

Thank you DrSql for the information. Where you say "and there are several scripts" are you saying to run the job as one with two steps or two seperate jobs? I' ve been reading a lot on the shrinking and realize that it only needs to be executed when necessary. Now I'm working on the word, necessary. Thanks again.

 

[DrSql]

I meant there are sveral way you can decide your re-index jobs and its available on-line.

What we have decided to deploy in an environment that has more than 200 instances is that

Re-Index Sun 1.00 AM
Update Stats: Thur 1.00 AM


Re-Index Configuration variables:
-- Less than 30% Fragmenation is an arbitrary decision point at which to reorganize indexes.
-- Greater than 30% is an arbitrary decision point at which to switch from reorganizing, to rebuilding.
-- 0 is the default fill factor. Set this to a value from 1 to 99, if needed we use 90 based on weekly growth treanding.




Dr.Sql
Good Luck.

 

[rileypetty]

Thanks, that clears it up for me.