SQL Server Maintenance Plan

Owned by Panagiotis Papanastasiou
Last updated: May 17, 2022 by Stelios Draganidis
4 min read

Introduction

The sql maintenance plan uses Ola Hallengren's optimization plan. If you already have it installed, you must uninstall it and then re-install it, if you want the latest version. We can not guarantee that all jobs will run successfully with an "old" implementation of the MaintenanceSolution.sql file that is installed on the server. If you do not have any MaintenanceSolution.sql installed, you can skip the uninstalling section.

Uninstalling existing MaintenanceSolution.sql

You can find the uninstall script on the FAQ on Ola Hallengren's website here. The direct link of the download is: https://ola.hallengren.com/scripts/misc/Uninstall.sql

Run the uninstall script so all old jobs are deleted and are created with the latest implementation. If you already have the latest version, this is not needed.

Screenshot from the FAQ page on Ola Hallengren's website.

Installing MaintenanceSolution.sql

1. Download MaintenanceSolution.sql installation

Navigate to Ola Hallengren's website and download the MaintenanceSolution.sql package. The direct link of the download is: https://ola.hallengren.com/scripts/MaintenanceSolution.sql

Screenshot from the front page on Ola Hallengren's website.

2. Open MaintenanceSolution.sql file

Open the MaintenanceSolution.sql on Sql Server Management Studio (double-click), and connect to the SQL Server you want to install the maintenance plan. The script is "using Master" in the beginning, so no need to change the database the script is targeting.

3. (Optional) Change script parameters

Change the configurable parameters on the script. You can change parameters like where the backup directory is, or the cleanup time (delete backup after X hours) i.e. retention time. Below, you can see the changes we made on interworks beta sql server.

Screenshot from interworks beta sql server configuration.

4. Run the installation script

Run the script and wait for the objects to be installed. This script should not take more than 10 seconds. This script creates stored procedures on your master database that contain the implementation for the MaintenanceSolution.

Screenshot from stored procedures created by the MaintenancePlan.sql

5. Create a maintenance plan

Navigate to SqlServer → Management → Maintenance Plan → Right Click → New maintenance plan → Name the new maintenance plan → OK

The new maintenance plan opens up. Double click on the subplan_1 template that is created automatically. It will open up the properties.

In this example, we will schedule the Index Optimization Plan. Change the name/description and set a scheduling according to your needs. In this tutorial, we set the index optimization to run at 01.00 AM every day.

Click OK on the new schedule, and save the sub-plan. Now we need to give the Maintenance Plan a task to run. From the Toolbox on the management studio (this should open when you open a maintenance plan), select Execute T-SQL Statement Task. Double click the new task on the maintenance plan, and set the T-SQL statement to the following:

EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'N'

This index optimization statement will run every day at 1 AM.

6. A complete maintenance plan example

Apart from index optimization, a comprehensive optimization plan with backups, cleanup etc, might look like this:

and have a schedule like this: