66chawger
asked on
SQL 2005 Maintenance Jobs (backup, index reorg, index rebuild, shrink, etc.)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ReOrg will create big tlog because it is logged operation irrespective of recovery model...
rebuild locks the table unless you use ONLINE indexing option...
rebuild locks the table unless you use ONLINE indexing option...
ASKER
Excellent feedback..thanks!
ASKER
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?