Updating Your Ola Hallengren Scripts

Introduction

If you are using Ola Hallengren’s free SQL Server Maintenance Solution to automate many important SQL Server maintenance tasks, you should try to keep them updated as much as possible. I am willing to bet that many SQL Server instances are running pretty old version’s of Ola’s code. Ola maintains a Version History page that has release notes and dates for each new version. This post will cover how to figure out if you have an old version and then how to go about Updating Your Ola Hallengren Scripts.

How Do I Know If I Have an Old Version?

One way to find out is to check the date in the version header comment in the four stored procedures that Ola creates in the master system database. These are the four stored procedures:

• CommandExecute
• DatabaseBackup
• DatabaseIntegrityCheck
• IndexOptimize

This is what the version header looks like:

Here is what it looks like in SSMS:

There is also a query that you can run to see the release date of the objects that Ola creates in the master system database. Running this query is faster than manually looking at the headers in the stored procedures. Ola originally wrote this query, but I have slightly modified it. You can get my version here.

-- Check version of Ola Hallengren's SQL Server Maintenance Solution 
DECLARE @VersionKeyword nvarchar(max);
SET @VersionKeyword = N'--// Version: ';

SELECT sch.[name] AS [Schema Name], obj.[name] AS [Object Name],
       CASE WHEN CHARINDEX(@VersionKeyword, OBJECT_DEFINITION(obj.[object_id])) > 0 
	   THEN SUBSTRING(OBJECT_DEFINITION(obj.[object_id]),CHARINDEX(@VersionKeyword,OBJECT_DEFINITION(obj.[object_id])) + LEN(@VersionKeyword) + 1, 19) END AS [Version],
       CAST(CHECKSUM(CAST(OBJECT_DEFINITION(obj.[object_id]) AS nvarchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS bigint) AS [Checksum]
FROM sys.objects AS obj
INNER JOIN sys.schemas AS sch 
ON obj.[schema_id] = sch.[schema_id]
WHERE sch.[name] = N'dbo'
AND obj.[name] IN (N'CommandExecute', N'DatabaseBackup', N'DatabaseIntegrityCheck', N'IndexOptimize')
ORDER BY sch.[name] ASC, obj.[name] ASC;

-- Version History
-- https://ola.hallengren.com/versions.html

-- If you don't have the latest version, you can download and install the latest version
-- https://ola.hallengren.com/scripts/MaintenanceSolution.sql

-- Make sure to change this line in the script so it doesn't create new Agent jobs: SET @CreateJobs = 'N'

Here is what it looks like in SSMS:

Once you know the release date of your version, you can check that against the Version History page to see if there is a newer version available. If there is, you can easily download the latest version, and then update your instance. Just make sure to make one change to Ola’s script before you do this!

Updating Your Ola Hallengren Scripts

During 2020, Ola released sixteen new versions of his SQL Server Maintenance Solution script. This was on top of seventeen new versions in 2019. If you have a SQL Server instance that has been around for a few years, it is likely that you are running a pretty old version of Ola’s code.

Fortunately, this is very easy to fix. The exact procedure is covered in his Frequently Asked Questions page (although it is not up to date since he dropped support for SQL Server 2005).

First, you need to download the latest version of the MaintenanceSolution.sql script. Then, before you run it, make one important change to the script!

All you have to do is change the DECLARE statement for the @CreateJobs variable to ‘N’ from the default value of ‘Y’. This line is located near the top of the MaintenanceSolution.sql script. Here is the exact line:

If you don’t make this change, running Ola’s script will drop and create all of the associated SQL Server Agent jobs, so you will lose any changes you might have made to the default parameters. You probably don’t want that…

Final Words

I recently wrote about a script that I use to create schedules for the Ola Hallengren Agent Jobs.

• Creating Schedules for Ola Hallengren’s Maintenance Solution

If you have any thoughts or questions about this post, please ask me here in the comments or on Twitter. You can also follow me on Twitter, where I am @GlennAlanBerry. Thank you for reading!