Create Maintenance Plan

If the need arises to apply maintenance to the databases on a regular basis, creating a maintenance plan can help to keep everything up-to-date.

Maintenance Plan Wizard

First things first. To be able to execute maintenance plans it is mandatory to have the SQL Server Agent running. Please consult paragraph SQL Server Agent below for more information on how to start the service if needed.

Complete the following steps to create a maintenance plan containing the following tasks:

• Check Database Integrity (useful for database backups)
• Reorganize Index
• Rebuild Index
• Update Statistics
• Maintain Cleanup Task (to clean up logging of the maintenance plan itself)

Note

The example maintenance plan runs on a weekly basis. Adapt the schedule as needed.

Also, perhaps there is no need to rebuild the indexes, only to reorganise them. Skip tasks if not required.

1. Open the item Management (1) and then right-click on Maintenance Plans (2).

   

2. Click on Maintenance Plan Wizard.

   

3. Click Next on the introduction dialog.

   

4. Fill in a good name for the plan, e.g. "Weekly Framework Maintenance", leave all other options unchanged and click on Next.

   

5. Click on the Change... button to open up the job schedule dialog. In the example, the schedule is set to run the job every Sunday at 1:00 a.m. Click OK to confirm.

   

6. Click on Next to continue.

   

7. Select all needed maintenance tasks, and click Next. Leave the order of execution and confirm with Next again.

   

8. Configure the Check Database Integrity task.

   

   Database selection (OmetaBAMLogging, OmetaFramework and OmetaDynamicCaseSystem):

   

   Leave other options as-is:

   

9. Configure the Reorganize Index task.

   

   Select the databases (OmetaFramework and OmetaDynamicCaseSystem).

   

   When multiple databases are selected, it is not possible to select individual tables. Keep the other settings as-is and click Next.

   

10. Configure the Rebuild Index task.

    

    Select the databases (OmetaFramework and OmetaDynamicCaseSystem). When multiple databases are selected, it is not possible to select individual tables. Keep the other settings as-is and click Next.

    

11. Configure the Update Statistics task.

    

    Select the databases (OmetaFramework and OmetaDynamicCaseSystem). When multiple databases are selected, it is not possible to select individual tables.

    Change the Scan Type to "Sample By" with 50%, and click Next.

    

12. Configure the Maintenance Cleanup task.

    Change the Delete files of the following type to Maintenance Plan text reports. Leave all other settings as-is and click Next.

    

13. A dialog is shown regarding reporting. This can be left alone or fully checked or unchecked. Confirm with Next.

14. In the final step, check whether all chosen settings for the new plan are correct, and confirm with a click on Finish.

    

SQL Server will now create the plan on the given schedule.

SQL Server Agent

The SQL Server Agent is the Windows service responsible for running job-related actions, including maintenance plans.

Therefor make sure the agent service is running when starting the creation of tasks and for running them.

Checking Run Status Of Agent

Open the SQL Server Management Studio and log on to the SQL Server host. Look through the items tree on the left and check whether the agent is active by looking for the item starting with SQL Server Agent. If the name of the item ends with (Agent XPs disabled), the agent service is not running and must be started.

Start Service

1. Right-click on the item and then click on Start from the popup menu.

   

2. Confirm the User Account Control dialog. Windows may skip this step if SQL Server Management Studio is running under sufficient access rights.

   

3. Click on Yes to confirm the start of the agent Windows service.

   

4. The agent item within SQL Server Management Studio should change to only SQL Server Agent as an acknowledgement that the agent process is running.

   

Additional References

For more details about maintenance plans and how to create maintenance plans, read the following section of Microsoft documentation: Maintenance Plans.