SQL Maintenance

If you have one of the full versions of SQL server, the SQL Management Studio has a section called "Maintenance Plans" that makes scheduling tasks like rebuilding indexes and updating statistics very easy.

From the SQL Management Studio, expand the "Management" section, and you will see "Maintenance Plans" under it.  Using the right mouse button, click on "Maintenance Plans", and you will get several options.  The simplest, is to choose "Maintenance Plan Wizard".

The Wizard will step you through everything to build, and schedule your plan.  To setup a rebuild indexes and update statistics, for example, you would click "Next" until you get to the plan properties page.

On the plan properties, name your maintenance plan, for example "Re-index".  It is useful to put a comment in for later reference, such as "Rebuild Indexes and Update Statistics".  Leave the option on "Single schedule", and click the "Change" button to schedule the plan.  Set the plan to "Recurring" and make sure it is enabled.  Set the schedule fields for what best suits your needs.  Weekly or daily would be appropriate in this case, depending on how active your database is.  See my blog on "SQL Performance" for a discussion on this.  Select :OK" when you have the schedule set, and then "Next" to go to the task selection.

For the tasks, select "Rebuild Indexes" and "Update Statistics".  The next page allows you to define the order that they process in.  Indexes should be rebuilt before the statistics are updated, so the default order is fine.  Next, will take you to the properties for each of the tasks.  Select "All" for the databases, unless you have a reason to only do specific ones.  The default values for the rest of the options should be okay.  

Going forward from there, you have the option to save results to a file, or email them.  Saving results is always a good idea.  Doing another "Next" brings you to the confirmation page, where doing a "Finish" will do the actual building of the maintenance plan, and scheduling it.

That's all it takes to setup and schedule most SQL maintenance tasks.  Other things, such as shrinking the database, or checking the integrity can be setup the same way.  Other SQL tasks that you do frequently can also be scheduled outside of the Wizard.  I will take a look at doing those in a future blog.

Dave.