Creating Schedules for Ola Hallengren’s Maintenance Solution

Introduction

Former Data Platform MVP Ola Hallengren has created and maintained his free SQL Server Maintenance Solution script for over twelve years now. His script creates some objects in the master system database (by default). It also creates and enables twelve SQL Server Agent jobs.  These jobs do things like database backups, index maintenance, and database integrity checks. It is a great solution that many Dr. DMV, LLC clients use. In order to have these actually jobs run, you will need to create SQL Server Agent job schedules for each one. This post covers Creating Schedules for Ola Hallengren’s Maintenance Solution.

Creating the Schedules

An enabled SQL Server Agent job that does not have a schedule associated with it will never automatically run. I frequently help clients setup and configure the Ola Hallengren SQL Server Maintenance Solution. After many years, I finally got tired of manually creating a job schedule for each of the twelve SQL Server Agent jobs. I decided to create a T-SQL script that you can run to create a schedule for each of the twelve jobs, which you can get here.

Default Job Schedules

These are the schedules that my script creates by default.

• CommandLog Cleanup job – Every Sunday at 12:01AM
• Output File Cleanup job – Every Sunday at 12:02AM
• sp_delete_backuphistory job – Every Sunday at 12:03AM
• sp_purge_jobhistory job – Every Sunday at 12:04AM
• DatabaseBackup – SYSTEM_DATABASES – FULL job – Daily at 12:05AM
• DatabaseIntegrityCheck – SYSTEM_DATABASES job – Daily at 12:10AM
• IndexOptimize – USER_DATABASES job – Daily at 1:00AM
• DatabaseIntegrityCheck – USER_DATABASES job – Daily at 2:00AM
• DatabaseBackup – USER_DATABASES – FULL job – Daily at 3:00AM
• DatabaseBackup – USER_DATABASES – DIFF job – Daily at 3:00PM
• DatabaseBackup – USER_DATABASES – LOG job – Every 15 minutes

Modifying the Schedules

You can (and probably should) modify the schedules in my script to suit your business requirements and infrastructure. For example, depending on your Recovery Point Objective (RPO) SLA, you would probably want to change how often you run transaction log backups. Another example is deciding when to run the most resource intensive jobs so they don’t overlap.  These would include the “DatabaseIntegrityCheck – USER_DATABASES” job and the “IndexOptimize – USER_DATABASES” job.

Final Words

If you have you have shared storage for your database servers, you will want to be even more careful about your job scheduling. This also comes into play if you are using Availability Groups. In either case, you will want to ensure that every instance is not doing resource intensive jobs at the same time. Please let me know what you think of this script and if you have any suggestions for improvements.

There is a companion post to this one called Creating Schedules for Ola Hallengren’s Maintenance Solution that you might find useful.

I recently wrote about an important security update for SQL Server 2012 and newer that you should try to test and install as soon as possible.

• Security Update for SQL Server 2012 Through 2019 (CVE-2021-1636)

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!