Create a Maintenance Plan

One of the first tasks we need to do after the installation of a SQL Server for Production is to create a Maintenance Plan. In the very far past, there were a lot of inconveniences and it was much better to create manually the Jobs for the different tasks but in the latest versions, I would say since 2008, it is very easy and well controlled by the Wizard, so, I will show the steps to do it.

First, we need to go to Microsoft SQL Server Management Studio / Management / Maintenance Plan / Maintenance Plan Wizard:

The system will show us the first window asking for a name and the user we want to assign the plan, we will choose the default option that should be the SQL Server Agent service account. The rest of options will be left by default. Ensure the option “Separate schedules for each task” is selected. Otherwise, you will be unable to schedule properly the Maintenance Tasks.

Then, the system will ask us for the Tasks we want to include in the Plan. We will leave as shown below:

Then, it asks us for the Order and again we will leave as it is.

Now, the system asks us which Databases we want to include in the plan for the first task: Check Integrity Task what will check the internal consistency for the data and index pages. We will choose as shown below to exclude the system DB’s as per the size of data and number of modifications, normally don’t need any Integrity Check.

In general, we recommend the same DB selection for all Tasks with a few exceptions. If you choose the option “These databases” be careful as a new Database will not be included in the plan that will need to be reviewed step by step and if forget to do that there will be no maintence in the new DB’s.

So, we recommend the same option for the Shrink Database Task. Then, leave by default the rest of fields. This option will take rid of the empty spaces that some updates and deletions leave in the DB.

And the same for Reorganize Index Task. This will Reorganize index while allows the system to work in the meanwhile.

Also Update Statistics Task, very important to run it periodically as it helps the Query Optimizer to find the best plan to run the Queries.

On next step, you need to know that the system saves a series of logs to register the steps performed. On this item: History Cleanup Task, the system removes the logs older than selected. We will re-visit this item once finished the Wizard to ensure all BAK & TRN’s are removed properly.

For Task Back Up Database (Full) the system will perform a full BackUp for each DB selected, so, on this case we recommend to backup All Databases as the system DB’s contain some data that if lost it may be a problem, for example, MSDB contains the information about the Jobs.

Note that there are two tabs, so, go to tab Destination and ensure the Folder for the BackUps is the correct one. For production, it always should be stored in a different disk than the database. Also, it is recommended to “Create a sub-directory for each database”. 

The rest of the options can be left by default but I would recommend to ensure that the Back Ups are compressed by right click on the server, click on Properties, then Database Settings and ensure the option Compress Backup is active, this will allow to make compressed backups by using a few more CPU resources but less Disk.

Next option is to define the BackUp for the Transaction Log, very important in production Databases to guarantee minimal data loss in case of incidence with any database. On this case we recommend All User databases (excluding system DBs). Also, need to review the Destination folder to the same folder where the Backup (Full) is stored and mark the option to “Create a backup file for every database”. Rest of options by default.

Next item to remove folders from the Maintenance, ensure the option “Include first-leven subfolders” is selected. Also select the folder where the BackUps are going to be stored.

Select Report Options, you may want to store the Report text file in the same main folder where you will store the backups. If you have configured the Database Mail, you may choose an operator to receive the notifications.

And click Finish to accept the selected options.

And the Maintenance Plan is saved.

Now, we have to set the Schedules, otherwise the plan will do nothing.

So, we need to open now the Maintenace Plan with the Modify option. Then, one by one in the tasks created with the Wizard, we can Schedule the timings when we want to run each plan.

Scheduling every task is very repetitive and to prevent system failures, it is recommended to set during the wizard but would make this tutorial too long, so, we will tell the main guidelines:

Check Database Integrity: This is time and resources consuming, so, recommended once per week when our DB has low usage. The same is valid for Shrink Database, Reorganize Index and Update Statistics. Try to find one Time Window for each task at any point when the DB has low usage and preventing two of these tasks to run at the same time.

Clean Up History and Maintenance Cleanup Task are low resources consuming, so, can run at any moment and can run once per day. Anyway, it is a good practise to run them when the workload is not high.

Backup Database (Full): Recommended once per day, when the workload is low.

Backup Database (Transaction Log): Recommended once every 30 minutes. On that way, if our system crashes, we will be able to go back at least on the last backup during the previous 30 minutes. Below the detail for the Schedule:

And… if you customize the names for every task, when saved the system will show a set of Jobs already scheduled for every task with the consistent names and will be very easy to follow-up any incidence during the execution.

I hope you will enjoy this post. Even if it looks very large, it reflects all important steps and should be more than enough for any medium company with Databases up to 100 Gb with 300 simultaneous users.

Now, we are re-visiting the Maintenance Cleanup Task to copy & paste the item created and review both as displayed below:

Click on the arrow for the first item and drag & drop over the second one to leave as in the previous image.

Then, double click on the first square to open the detail and leave as the image below to ask the system to remove .BAK files older than 23 hours on the BackUp folder:

And the same with the .TRN files:

Obviously the retaining time will change depending on the resources available and the criticallity for the DB’s behind.