January 9, 2008 at 9:45 am
Hi,
I hope its not a dumb question, but wanted to get your expert opinion on the sequence in which the various tasks needs to be run while setting up a maintenance plan
1) BackUp Database
2) Check Database Integrity
3) Shrink Database
4) Reorganize Index
5) Rebuild Index
6) Update Statistics
7) Maintenance Cleanup
8) History Cleanup
The reason why i was checking is that I have read articles where the Backup task is done after all the reorganizing of the indexes as well as the updation of the Statistics
Please do advice
Thanks
Vinu Verma
January 10, 2008 at 7:15 am
As always you could answer it depends, but personally I would never do all these tasks in one maintenance plan. Also the frequency of some tasks should be different.
Backups for example should be taken at least daily, while reorganizing indexes in most cases once per week is more than enough. Depending on the activity and size of a database you might want to reindex only certain tables or indexes.
Update statistics is something I usually don't include in maintenance plans. Even though some might have a different opinion on this, but for the database option AUTO UPDATE STATISTICS is sufficient.
You also list SHRINK DATABASE which I would never include in a maintenance plan. Sear for shrink database and you will find hundreds of post why you shouldn't do it.
Hope this helps
[font="Verdana"]Markus Bohse[/font]
January 10, 2008 at 8:12 am
I agree, those tasks should not be lumped together (despite that you see them on the same dialog window quite often)
You should separate into
Backup Maintenance - backup, clean old backup, verify integrity
Maintenance maintenance - clean maint. plan history
Performance maintenance - re-organize index, update stats, shrink
Personally
Auto Update Statistics is sufficient unless you encounter a significant discrepancy on the query optimizer plan
Shrink Files - only do it when necessary, and backup first
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply