Before any maintenance tasks can be run, SQL Server Agent must be running first. Within SQL Server Management Studio, expand the Management folder within the Object Explorer window and ensure that this service is running.

NOTE: This article is for information purposes only to assist with various troubleshooting techniques. As per the Business Partner handbook, Sage 200 Technical Support do not support SQL server directly. Any further queries surrounding the set up or running of Maintenance Plans within SQL Management Studio should be directed to your IT department, database administrator or Microsoft.

NOTE: This article has been written using Microsoft SQL Management Studio 2012 as an example. The process may differ slightly for other versions.

How to create a Maintenance Plan

1. Within Object Explorer, expand the Management folder, right click on Maintenance Plans and select New Maintenance Plan...
   
   

   

2. The SQL Server Maintenance Plan Wizard screen appears. If the starting page is shown, select Next.
   
   
3. Give the Maintenance Plan a name. You may wish to enter a description as well before selecting Next. We recommend at this stage that you do not set a schedule unless you are fully aware of the best situation to run such as a schedule. You should confer with the customer or their IT support team where necessary if there is no regular plan already in place.
   
   

   
   
   

4. Select the checkboxes for Reorganize Index and Rebuild Index > Next > Next.
   
   

   

5. From the Databases: drop-down list, select 'These databases:' and select the checkbox against the name of the database you wish to include in the Maintenance Plan > OK > Next.
   
   

   

   
   

   TIP: You may select more than one database within the same Maintenance Plan but this may impact on performance whilst the Maintenance Plan is running and may take substantially longer to complete.

6. Repeat the previous step for the next screen - Define Rebuild Index Task.
7. If you wish to enable reporting of the Maintenance Plan, enter the desired path to store the file or untick the checkbox and select Next.
8. To create the Maintenance Plan, click Finish. The progress screen is displayed. Once complete, select Close.
   
   

   
   
   

   This process may take several minutes depending on database size and the number of databases chosen.

   To run the plan, right-click on the name of the plan created above and select Execute.
   
   

   

9. A progress screen will be shown whilst the Maintenance Plan is executed. Once complete, select Close.