KB00026: Create a SQL Server Maintenance Plan

Description

You notice that your MessageMaster database is growing larger, and you recognize that like any well-used database, this one too will require a maintenance plan to keep healthy and performing efficiently.

Disclaimer

The details of SQL optimization and maintenance are beyond the scope of Objective Software's documentation; please see your SQL administrator. What is provided here is just a guideline to get you started with a basic maintenance plan for your ObjSoft database.

Resolution

Database Properties

First, make sure you set the Options on your database as follows:

Full recovery mode allows the maintenance plan to succeed without the database having to be in single user mode. Be sure to check Auto Shrink.

Setting up the Maintenance Plan

Here are steps to set up a basic maintenance plan for the MessageMaster database. The database must be called ObjSoft.

1. Download the SQL script that will create your maintenance plan here. Save it where you can access it from your MessageMaster server.
2. Open SQL Query Analyzer. Be sure to authenticate with sufficient permissions to administer the SQL server instance. Point to the 'master' database.

3. Load the ObjSoftDBMaint.sql script from where you downloaded it. Near the top, modify the value for the indicated parameter, making sure it points to where you want to store the backup files and job log files.

   Notes:

   • Make sure that a directory named BACKUP exists under the directory you specify
   • Backup files will be retained for a limited time, old ones being replaced by new ones as they come

4. Run the script to have it create the maintenance plan and job that goes with it.
5. In SQL Enterprise Manager, ensure the plan and job has been created. Check the properties of the job, and make sure that the 'owner' is set to a user that has sufficient rights to execute the job. You may need to change it to 'sa'.
   
6. Run the job to make sure it works successfully.
   
7. Make sure that the SQL Server Agent service is running - it is required for the scheduling of your maintenance plan job. In the Services applet, make sure that the SQL Server Agent is set to Automatic start, not Manual or Disabled.

Applies To

• MessageMaster Server