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:

Version Header Comment

Here is what it looks like in SSMS:

Updating Your Ola Hallengren Scripts
Version Header in Ola Hallengren Stored Procedure

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:

Updating Your Ola Hallengren Scripts
Checking Your Ola Version 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:

Updating Your Ola Hallengren Scripts
@CreateJobs Variable Set to ‘N’

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.

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 @GlennAlanBerryThank you for reading!

SQL Server

8 thoughts on “Updating Your Ola Hallengren Scripts

  1. And be sure to re-apply any of your necessary, maybe dirty fixes to the procedures.
    E.g. DatabaseBackup falsely tries to take transaction log backups on a forwarder of a distributed availability group.
    So we had to fix this procedure to work on forwarders, but this fix does not fit well to the rest of the procedure.
    It does not work on global primary and hence is not ready for a pull request into Ola Hallengren’s master.

    1. Yep, if you have modified Ola’s code in the stored procedures, this will overwrite it.

  2. dbatools will also update Ola’s solution for you – useful if you have many instances 🙂

    https://docs.dbatools.io/#Install-DbaMaintenanceSolution

    1. That is true. Thanks for reminding me!

  3. If you are running a REALLY old version, some of the parameter names have changed over the years (Minimum number of pages is one off the top of my head)

    1. That is a good point. Thanks!

  4. Hi Glenn

    Nice post, thank you.

    Too, depending where you ran the procedures first time (master, custom db, etc), then you should update new version over there preferably.

    Alvaro

    1. You could also delete the old stored procedures from whatever database they are in, and then run Ola’s script to put the new versions in master.

Leave a Reply

%d bloggers like this: