Ola Hallengren's Maintenance Solution: Getting Started

I gave up on Maintenance Plans after one too many index rebuilds that ran all-or-nothing and a backup cleanup task that quietly did nothing. Ola Hallengren's Maintenance Solution is a free set of stored procedures for backups, integrity checks and index maintenance, and it's what I install on every instance I'm responsible for. It's been around for years and a huge number of shops use it.

It works on SQL Server 2008 through 2017, any edition. Express has no Agent, so schedule with Task Scheduler there. You need sysadmin and a backup target, either a local path like D:\Backup or a UNC share the service account can write to.

Download and edit the script

Download MaintenanceSolution.sql from ola.hallengren.com. Open it in SSMS and look at the top of the script. Three lines need your attention:

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

SET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.
SET @BackupDirectory     = N'D:\Backup' -- Specify the backup root directory.
SET @CleanupTime         = 72           -- Time in hours, after which backup files are deleted.
SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable          = 'Y'          -- Log commands to a table.

I usually create the objects in a dedicated DBA database instead of master, so they survive a master rebuild and are easy to back up on their own. Change USE [master] accordingly and create the database first.

Run it

Execute the whole script once. It creates:

• dbo.CommandExecute — runs and logs individual commands
• dbo.DatabaseBackup — full, differential and log backups
• dbo.DatabaseIntegrityCheck — DBCC CHECKDB and friends
• dbo.IndexOptimize — index rebuild/reorganize and statistics updates
• dbo.CommandLog — a table with every command executed, its duration and any error
• Eleven SQL Server Agent jobs, all disabled and unscheduled

Look at the jobs before you enable them

The defaults are sensible, but I read every job step once. Open DatabaseBackup - USER_DATABASES - FULL and look at the step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

The step is a CmdExec step calling sqlcmd so that output can be written to a file. Add @Compress = 'Y' on Standard and Enterprise editions; backup compression is supported there since 2008 R2 and there is rarely a reason not to use it.

The jobs you will schedule on a typical OLTP server:

Add a schedule to each in SSMS (Job Properties → Schedules) and enable them.

IndexOptimize

The default step does index maintenance only. I also update statistics, which in my experience does more good than rebuilding indexes. Change the command to:

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,
    @MinNumberOfPages = 1000,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @TimeLimit = 14400,
    @LogToTable = 'Y';

@TimeLimit in seconds stops the job from running into business hours. @OnlyModifiedStatistics = 'Y' skips statistics that have had no changes since the last update.

DatabaseBackup

A few more parameters worth knowing:

EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES, -ArchiveDb',   -- exclude a database with a minus sign
    @Directory = N'\\backupserver\sqlbackup',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @Verify = 'Y',
    @CheckSum = 'Y',
    @CleanupTime = 168,
    @NumberOfFiles = 4,
    @LogToTable = 'Y';

@NumberOfFiles stripes the backup across several files, which speeds up large backups considerably. @CleanupTime deletes files older than that many hours, but only after the current backup succeeded and was verified.

First run

Start the full backup job by hand and check the log table:

SELECT ID, DatabaseName, CommandType, StartTime, EndTime, ErrorNumber, ErrorMessage
FROM DBA.dbo.CommandLog
ORDER BY ID DESC;

Every row should have ErrorNumber 0. The folder structure created under @Directory is ServerName$Instance\DatabaseName\FULL\, with file names containing server, database, type and timestamp.

Notes on things that failed for me

• Msg 50000: The directory D:\Backup does not exist. DatabaseBackup checks the root directory before doing anything. Create it and make sure the SQL Server service account, not your own account, can write there.
• The job fails immediately with The step did not generate any output. Process Exit Code 1. Usually the sqlcmd call cannot connect using -E because the Agent service account has no login. Grant it sysadmin or switch the step to a T-SQL step.
• ALTER INDEX failed because ... ONLINE option is not available in this edition on Standard Edition. IndexOptimize normally falls back to offline when you list both, but if you only specified INDEX_REBUILD_ONLINE, add INDEX_REBUILD_OFFLINE after it.
• Backups of the same database piling up and never deleted. @CleanupTime only applies when @Verify = 'Y' succeeds and matches on the exact directory structure. If you moved the backup root, old files under the previous path are not touched.

After that I mostly forget about it. I glance at CommandLog once a week and re-run the script when a new version comes out; it upgrades the procedures in place.