Post reply

Maintenance Plan

  • August 24, 2009 at 9:31 am

    #225404

    Hi people!

    I'm wondering how the best way to create a Maintenance Plan is. By this what I mean is, the order in wish the different steps are put together etcetera.

    Right now I have a MP that goes like this:

    1-Check DB integrity

    2-Update statistics

    3-Full DB Backup

    4-Clean Backup Files(older than 2 days)

    5-Reorganize Index

    6-Update Statistics

    First thing I know is weird is that the Update statistics step appears twice, I really don’t remember what was that I was thinking the day that I did this MP but for some reason let it like that, but I will appreciate the guidance about where that step should really be.

    About the backup step it does a full Backup here but I have another MP doing Transaction logs Backup from 8 am to 10 pm every hour and I plan to add a Differential Backup around 3pm.

    As always, any help that anyone can give will be appreciated.

    Thanks for the help.

    FJM

  • August 24, 2009 at 3:31 pm

    #1043908

    I am not sure that there is reason to fire of #2.....I would just drop it.

    beside that I would leave the way it is

  • August 24, 2009 at 3:41 pm

    #1043914

    I think at the time I did it so when the backup gets done de DB already had the statistics updated.

  • August 25, 2009 at 6:07 am

    #1044093

    If you are rebuilding index then you wont have to do update statistics manually in your maintenance plan.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • August 25, 2009 at 6:15 am

    #1044097

    Also, you might want to perform a bit of clever index maintenace.

    You might want to reorganise indexes with fragmentation below 30% and rebuild indexes with fragmentation above 30%.

    Something like that....

    Vivek Shukla - MCTS SQL Server 2008

  • August 25, 2009 at 6:44 am

    #1044117

    I don't see where to add those parameters on the MP. Do I have to do it with code?

  • August 25, 2009 at 6:51 am

    #1044122

    fjmorales (8/25/2009)

    I don't see where to add those parameters on the MP. Do I have to do it with code?

    This url from msdn side has a nice example of doing this.. see example D.


    Pradeep Singh

  • August 25, 2009 at 6:59 am

    #1044128

    I think you left out the url...

  • August 25, 2009 at 7:00 am

    #1044130

    No it's present. Click on the word This


    Pradeep Singh

  • August 25, 2009 at 7:02 am

    #1044134

    oh...

  • August 25, 2009 at 3:36 pm

    #1044569

    The only problem with that example is its using cursors and people dont really like them because of their resource hungry nature.

    I'd say replace this cursor with better code to make it faster and less resource intensive code.

    Vivek

    Vivek Shukla - MCTS SQL Server 2008

  • August 25, 2009 at 3:37 pm

    #1044570

    Also, go through following. its quite helpful article.

    http://technet.microsoft.com/en-us/library/cc966523.aspx

    Vivek Shukla - MCTS SQL Server 2008

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply