Create a SQL Server database maintenance plan

Database maintenance plans automate many database management tasks in Microsoft SQL Server. You can create maintenance plans with a simple wizard-based process without any knowledge of Transact-SQL.

You can perform the following tasks as part of a database maintenance plan:

  • Database compression

  • Database backup

  • Run Operator Notification

  • Update database statistics

  • Database Integrity Check

  • Clean up remaining maintenance files

  • Run a SQL Server Agent job

  • Executing a Transact-SQL statement

  • Index Recovery

  • Index reorganization

  • Clear Database History

Content
  1. Run the Database Maintenance Plan Wizard
  2. Name the database maintenance plan
  3. Plan your database maintenance plan
  4. Select tasks for your maintenance plan
  5. Order jobs in a database maintenance plan
  6. Customize plan task details
  7. Select your maintenance plan reporting options

Run the Database Maintenance Plan Wizard

Open Microsoft SQL Server Management Studio (SSMS) and expand the Administration folder. Right click on folder Service plans and select Maintenance Plan Wizard in the pop-up menu. You will see the wizard home screen as shown above. Click Further Get on.

Name the database maintenance plan

On the next screen that appears, provide a name and description for your database maintenance plan. Here you should provide information that will be useful to another administrator (or yourself!) trying to figure out the purpose of the plan over months or years.

Plan your database maintenance plan

You will probably want to use the default option here One schedule for the whole plan or no schedule . You can create different schedules for different tasks, but we recommend that you create different schedules for different schedules to make it easier for you.

Press the button Change to change the default schedule and select a date and time for the plan to run. Press the button Further when you’re done.

Select tasks for your maintenance plan

You will see the above window. Select the tasks to include in your database maintenance plan. When you’re done, click the button Further Get on.

Order jobs in a database maintenance plan

In the next window shown above, you can change the order of tasks in the maintenance plan using the buttons Go up and Move down .

Customize plan task details

Then you have the option to adjust the details of each task. The options presented to you depend on the tasks you select. The image above shows an example screen used to set up a backup job. When you’re done, click the button Further Get on.

Select your maintenance plan reporting options

Finally, you have the option to have SQL Server generate a report with detailed results every time the plan is run. You can email this report to the user or save it as a text file on the server.

Leave a Reply

Your email address will not be published.