- Open up SQL Server Management Studio.
- Expand out "Management".
- Right Click on "Maintenance Plans".
- Choose "Maintenance Plan Wizard".
- Give your maintenance plan a name. Suggestion: Name your plan something that describes what it does. For example, you could give it the name "Rebuild Indexes on the fly".
- Click Next
- Check "Rebuild Index" and "Update Statistics"
- Click Next
- "Rebuild" should be at the top
- Click Next
- Choose under Databases: All user databases
- Click Next
- Choose under Databases: All user databases
- Click Next
- Note or Change the location of the report of this task
- Click Next
- Click Finish
- Final screen upon successful creation
- Now you will have your task created under Maintenance Plans
- Right click on your maintenance plan, it will show the name you gave it in step 6
- Click Execute
Note: If it fails immediately check your SQL Agent, it should be green as you see below with a little play button. If it is not started do so then try 22 and 23 above again.
If you have any trouble with getting this to run we will need to engage our services group as a paid engagement.
Overview
The purpose of this article is to provide the basic steps of creating a maintenance task within SQL Server which is crucial to the success of heavily coded or large databases. It is recommended to run this in accordance with your maintenance plan. A maintenance plan is suggested in our specifications guide. For a custom maintenance plan please contact our services department.