SQL 2005 Maintenance Jobs (backup, index reorg, index rebuild, shrink, etc.)

Posted on 2008-11-18
Last Modified: 2012-06-21
I have some general questions about SQL Maintenance Jobs.   Currently, I have a separate backup job for each of our DB's, 3-5 jobs, which also notify a group via DBMAIL upon failure.   Now, I want to introduce Index rebuild, Index Reorg, and Shrink plans.   So here are my questions:
1).  Would it be better to have my backup jobs under one maintenance plan and w/ multiple subplans? The backup jobs are scheduled daily and run in the evening.
2).  I know the difference between Index Rebuild and Index Reorg and that with SQL 2005, you can do an index rebuild online leaving the index available.  Which one is better?  How often do you would you want to run either? I assume weekly or on demand depending on performance or size issues.
3).  For the DB Shrink, this could be set to run weekly and before the backup, so when the backup job runs, it will pick up the smaller DB?  Can it be be a subplan of the db backup job and have a constraint for the backups to start once the DB shrink subplan is completed?
4).  Is there any specific order these four maintenance plans/subplans should be run?  

Any specifics on the parameters in the index rebuild wizard or t-sql would be appreciated.  
Question by:66chawger
    LVL 15

    Accepted Solution

    It is better to have to different jobs/plans for backup and maintenance (reindex).
    Online reindexing is only available in EE and it has some ristrictions like table should not have text/varchar(max) columns...
    It is better to reindex the table which fragmented instead of doing all without checking the fragmetation.
    DB shrink is not advisable because, if may grow again and can impact the peformance...

    YOu can also you use the script to reindex instead of maintenance plance...

    Author Comment

    Very interesting!!!  When you speak of reindexing are you speaking in general, or about rebuild vs. reorg?  I have had luck performing index reorg test, but as you said about the online reindexing (assume you are speaking about rebuild) I have encountered errors.  This could be related to what you mentioned about tables that should not have text/varchar (max) columns....

    Ok, what is the best way to look at fragmentation do decide if it is feasible to do a index reorg?  
    So you would not advise doing DB shrink as part of a maintenance job, correct?  So other than backup plans, what other maintenance plans would you suggest if not index reorg/rebuild and shrink?

    Author Comment

    I spoke too soon!  I see you gave me a link to check fragmentation.  Is it true that index reorganization, not rebuild, locks the table?
    LVL 15

    Expert Comment

    ReOrg will create big tlog because it is logged operation irrespective of recovery model...
    rebuild locks the table unless you use ONLINE indexing option...

    Author Closing Comment

    Excellent feedback..thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now