Microsoft SQL Server maintenance plans

It is recommended that a nightly maintenance task schedule be setup using Microsoft SQL Management Studio. The following article describes how to setup a Microsoft SQL maintenance plan to perform some of this maintenance on a scheduled basis. The maintenance plan we recommend is to rebuild the database indexes and update database statistics. Over time these can become out of synch with the databases and cause performance issues. You will want to update statistics and indexes on all 4 FileHold databases.

Do not perform this work if you are not comfortable with Microsoft SQL Server and Windows server administration.

The Express version of SQL Server does not provide any way to automate the execution of maintenance plans. You will need to run these manually or use a custom or third party tool to automate their execution. A good resource for working with Express is expressdb.io (this web site is not associated with FileHold).

To create Microsoft SQL Server maintenance plan

In this example, a plan is set up as a recurring daily plan at midnight.

  1. Enable SQLSERVERAGENT in Services.msc.
  2. Set to Startup type to Automatic.
  3. Open Microsoft SQL Server Management Studio.
  4. Go Management > Maintenance Plans. Right-click and select Maintenance Plan Wizard.
  5. The SQL Server Maintenance Plan Wizard window opens. Click Next.
  6. Enter a Name and Description for the Maintenance Plan.
  7. Select Single schedule for the entire plan or no schedule.
  8. Click Change to set up a schedule.
  9. In the New Job Schedule screen, set the schedule type to Recurring.
  10. Ensure the Enabled check box is selected.
  11. Choose a frequency that is appropriate to the rate of change in your data. For example, set the frequency to Daily at 1 time a day.
  12. The time for the maintenance plan should take into account the length of time it typically takes to run and other activities that may run during low usage times for the system. For example, in the Daily Frequency area, set the time to 1 AM.
  13. Set the Start date to today's date.
  14. Click OK.
  15. In the Maintenance Plan Wizard window, click Next.
  16. Select the Rebuild Index and Update Statistics check boxes in the Maintenance Tasks list.
  17. Click Next to configure Rebuild Index Maintenance Tasks.
  18. In the Maintenance Task Order screen, leave the order as is and click Next.
  19. In the Define Rebuild Index Task screen, select the following databases from the list: ch_documentrepository, ch_fulltextsearch, ch_librarymanager, ch_userrolemanager and click OK.

The database prefix "ch_" is the default. Yours may be different if you chose the option to change the prefix at installation time.

  1. In the Advanced Options area, select the Keep index online check box.
  2. Click Next.
  3. In the Define Update Statistics Task screen, select the following databases from the list: ch_documentrepository, ch_fulltextsearch, ch_librarymanager, ch_userrolemanager and click OK.
  4. Click Next to set up the report and email options. Configure as needed.
  5. Click Next.
  6. In the Complete the Wizard window, review a summary of your settings and click Finish.
Image
SQL Maintenance Plan Wizard
  1. The tasks will be added and scheduled in the Maintenance Plan Wizard. Click Close when complete.
  2. The maintenance plan will then be updated in Microsoft SQL Management Studio and the SQL Server Agent Jobs will be updated.
  3. Monitor the maintenance plan on a periodic basis to make sure that the plan is functioning normally.