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
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!