Review: Ola Hallengrens Maintenance Solution

SQL Server offers out-of-the-box solution to create a workflow of tasks that can be used to optimize, backup and run consistency checks on your databases. These workflows, commonly known as Maintenance Plans, are actually Integration Services packages that are run either by scheduling them as SQL Server Agent jobs or manually. While I wholeheartedly recommend that you run regular backups and other maintenance routines to your databases, I wouldn’t necessarily recommend using Maintenance Plans for this. In fact, I’d probably never recommend using Maintenance Plans, unless it’s the only thing you have.

If the Maintenance Plans are the only thing you have right now, I have good news for you. There is another option and it’s a very good one, Maintenance Solution by Ola Hallengren. So how good it is, you ask? For starters, it has been a Gold winner of SQL Server Magazine Awards from 2010 to 2013 and I’ve had people from Microsoft recommend using it, that’s how good it is.

Installation

The installation couldn’t be much simpler. Go to Olas website, download the MaintenanceSolution.sql script and choose a database you want to install the objects into (note that it will install into master as default). Personally I prefer to use a utility database for all my maintenance needs over existing system databases. If you want to, you can also download the maintenance stored procedures are separate scripts, just make sure that you also grab the CommandExecute as that is required by all the maintenance stored procedures!

Current version of Maintenance Solution is compatible with all SQL Server versions from 2005 to 2014.

Usage

The usage is rather straightforward as well, run the stored procedure with the parameters you want. Most of these stored procedures have certain defaults set, such as the backup stored proc where the minimum amount of parameters you need to set is the @Databases and @BackupType (Full, Log , Diff). That will create a backup to your default backup directory though, which is probably not what you want. You might also want to set up some cleaning to be done for the old backups, compression etc. Index optimization and integrity check procedures will both run just with @Databases parameter set, but again, not probably not what you want.

Ola kindly provides us with an extensive “Help” section in his page for all the procedures that explain the various parameters and what are the defaults. There’s a good number of parameters to work with (over 30 with Backup alone) so I’d warmly recommend looking at the “Help” pages, they do an excellent job of explaining what all the various parameters mean.

My favorite features

Look, an error!
Look, an error!
Olas solution has many excellent features, but there’s one that I really like. All these procedures have ability to log their results to a table when using @LogToTable parameter. This isn’t something I do always, but if you’re running into issues with maintenance tasks it’s simple to turn this option on and get an easy access to information about the execution of the procedures.

The information that’s logged has the error numbers, error message if there was any, start and end times, database, schema and objects etc. Basically there’s all the information there you need to solve issues with maintenance tasks and it’s easy to find. As seen in the screenshot, DBCC CHECKDB has failed due to running out of space in the TEMPDB transaction log (the infamous 9002 error).

As this is very likely my last blog post before the holidays, I wish you all Merry Christmas!

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

2 thoughts on “Review: Ola Hallengrens Maintenance Solution”

  1. whoah this weblog is fantastic i love studying your posts.

    Keep up the great work! You understand, many persons are searching round for this information, you can aid them greatly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s