Setting Up a Maintenance Plan

If you are checking in a high volume or backlog of documents to Webdocs, the indexes Microsoft SQL Server uses to look up documents may become fragmented. Fragmented indexes decrease database performance and slow down searches. To improve database performance, you need to reorganize the indexes.

This appendix shows you how to set up a maintenance plan in Microsoft SQL Server Management Studio to regularly reorganize the indexes.

NOTE:

Depending on your version of Microsoft SQL Server Management Studio, some screens may look different.

To create a maintenance plan for Webdocs for Windows:

  1. Open Microsoft SQL Server Management Studio and log in to the SQL Server instance for the Webdocs database.

  1. Run the Maintenance Plan Wizard:

    1. In Object Explorer to the left, expand Management.

    2. Right-click Maintenance Plans and choose Maintenance Plan Wizard.

      The Maintenance Plan Wizard opens with Select Plan Properties showing.

  1. Choose the plan properties:

    1. In Name, type a name for the maintenance plan.

    2. Next to Schedule, click Change.

      New Job Schedule displays.

    1. Choose a schedule for this maintenance plan.

      NOTE:

      The higher the volume of documents checked in to Webdocs, the more often you should reorganize the indexes.

    2. Click OK.

      The schedule you chose displays in the Schedule field of the Maintenance Plan Wizard.

    3. Click Next.

      Select Maintenance Tasks displays.

  1. Set the maintenance plan to reorganize the index:

    1. Check Reorganize Index.

    2. Click Next twice until you get to Define Reorganize Index Task.

  1. Choose to reorganize the Webdocs database.

    In Databases:

    1. Choose These databases.

    2. Check webdocs.

    3. Click OK.

    1. In Define Reorganize Index Task, make sure Object is set to Tables and Views.

    2. Click Next.

      Select Report Options displays.

  1. (Optional) In Select Report Options, choose how to receive reports on the maintenance plan and click Next.

    Complete the Wizard displays.

  1. Finish creating the maintenance plan:

    1. Click Finish.

      The Maintenance Plan Wizard displays its progress.

    2. When the maintenance plan is successfully created, click Close.

      The maintenance plan displays in Maintenance Plans in Microsoft SQL Server Management Studio.

  1. Scroll to the bottom of Object Explorer and make sure SQL Server Agent is started:

    • If there is a green arrow next to SQL Server Agent, it is started.

    • If there is a red X next to SQL Server Agent, right-click SQL Server Agent and click Start.

      NOTE:

      If you are connected to a remote SQL server, the red X and green arrow will not display and you cannot control SQL Server Agent.

The maintenance plan is now set up to reorganize the indexes on the schedule you chose in step 3a.