So You Need A Maintenance Plan?


I mean it this time when I say this will be a quick one. For most DBAs, this information is already a part of your bread and butter. But for some accidental DBAs, this information may be useful.

One of the first things that was drilled into my brain was to ensure that we had a solid maintenance plan for our SQL Servers, complete with a consistent backup schedule and troubleshooting tools to assist when a problem arose. Luckily, the SQL Community has developed many free, open source tools that are widely used across many industries. Today, we are looking at one of the most importance tool of them all.

Ola Hallengren’s Maintenance Solution

https://ola.hallengren.com/

This is the first thing I deploy to my new servers after SQL Server has been installed. This sets up various jobs (backups, integrity checks, reindexing, etc) that you can easily schedule to your hearts content. The parameters are WELL documentated and the community can assist with anything you run into if you find an issue installing the plan. Out of the box, this is solid but the customization is endless. Below are the general parameters that you SHOULD look at when installing the plan, as your needs will be different than mine.

USE [master] -- Specify the database in which the objects will be created.

1) I use a separate database, like a “DBADMIN”, to store all my tools in rather than master. But if you use master, just keep it set to this.

DECLARE @CreateJobs nvarchar(max)          = 'Y'         -- Specify whether jobs should be created.

2) If this is your first install, leave this to ‘Y’. If you are updating your plan, due to an updated version pushed out by Ola, make sure to switch this to ‘N’.

DECLARE @BackupDirectory nvarchar(max)     = NULL        -- Specify the backup root directory. If no directory is specified, the default backup directory is used.

3) I strongly suggest backing up your databases to another server, and put the network path here. This will set the default backup path for your jobs. So change NULL to that path.

DECLARE @CleanupTime int                   = NULL        -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

4) For me, 72 hours works here. But that does not mean the need of your client/your customer will be the same. Make sure to ask them! And if they don’t tell you, then maybe it is worth the effort to sit down with them to discover your RTO/RPO. (More on that in another post?)

DECLARE @OutputFileDirectory nvarchar(max) = NULL        -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.

5) Find a location on your server to output a seperate log of this plan. You want this, trust me. With the path, you can always take a look at the log to see why a job failed in an easier to read format 🙂

DECLARE @LogToTable nvarchar(max)          = 'Y'         -- Log commands to a table.

6) I always leave this as “Y” as there are times where I need to see what commands were passed (in case I screwed something up) in a previous job execution. The table will be created under whatever database you have installed the plan in. 

That Wasn’t So HARD!?

After that, go ahead and execute the plan and now you have a solid maintenance plan! Go ahead and adjust the schedules to your liking, and make sure to include your operator in those notifications if the job fails!