A SQL Database Maintenance Plan
Previously, I gave an overview of the tasks that should be done to keep your SQL system running efficiently. These are rebuilding indexes and updating statistics, backing up, and shrinking your database.
On a nightly basis, I recommend backing up your database first. By backing up first, if something drastic occurs during any of the other steps, then you have a backup of your most recent data to restore. While I have never seen this occur in the other processes, leaving a database unusable, it is best to be safe.
Also, I prefer to setup the maintenance plans so that each step needs to complete successfully, in order to proceed to the next. Then, if any error does occur, the processing immediately stops, and there is no risk of compounding the issue that caused the problem.
After the backup completes each night, then do the rebuilding of the indexes, and updating of the statistics. The rebuilding of the indexes should occur before the updating of the statistics. This allows the statistics updating task to use the freshly built indexes, and give the best performance.
Again, I recommend the backup database, followed by the rebuild indexes, and update statistics, be done every night. This way, you have current backups at all times, and your SQL database will have consistent good performance.
The shrinking of the database would be done once a week, or less frequently if appropriate, after the nightly tasks are done. I always schedule this after the nightly tasks. The reason for this is that this is the step that has the least impact on performance and data safety.
So, I always backup first, and make sure that I have a good snapshot of my current data. Then, rebuild indexes and update statistics, which usually has the biggest impact on improving performance. Then, periodically shrink and reorganize the database, to get the bit of performance improvement.
Dave.