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 tune Microsoft SQL Server
- Enable SQLSERVERAGENT in Services.msc.
- Set to Startup type to Automatic.
- Open SQL Management Studio.
- Expand Management > Maintenance and right click on Maintenance and select Maintenance Plan Wizard.
- The SQL Server Maintenance Plan Wizard window opens. Click Next.
- Enter a Name for the Maintenance Plan. In this example, will set up a recurring daily plan at midnight.
- Select Single schedule for the entire plan or no schedule and click Change to set up a schedule.
- In the Schedule Job Properties window, set the schedule type to Recurring.
- 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.
- 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.
- Set the Start date to today's date.
- Click OK.
- In the Maintenance Plan Wizard window, click Next.
- Select the Rebuild Index and Update Statistics check boxes in the Maintenance Tasks list.
- Click Next to configure Rebuild Index Maintenance Tasks.
- To define the settings for the Rebuild Index portion of the maintenance plan, click the arrow in the Databases field.
- Select These databases and select the four FileHold databases: 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.
- In the Advanced Options area of the Define Rebuild Index Task window, select the Keep index online check box.
- Click Next to configure the Update Statistics Maintenance Task settings.
- Click the arrow in the Databases field.
- Select These databases and select the four FileHold databases and click OK.
- Click Next to set up the report and email options. Choose which options you want.
- In the Complete the Wizard window, review a summary of your settings and click Finish.
- The tasks will be added and scheduled in the Maintenance Plan Wizard. Click Close when complete.
- The maintenance plan will then be updated in Microsoft SQL Management Studio and the SQL Server Agent Jobs will be updated.
- Monitor the maintenance plan on a periodic basis to make sure that the plan is functioning normally.