How to configure Ola Hallengren SQL Server maintenance solution

Mark Varnas
November 19, 2020
One Comment

If you are a DBA, you understand the importance of having a robust Database Maintenance Solution.

Ola Hallengren has developed an excellent maintenance implementation using T-SQL scripts. It’s a free solution replacing the traditional GUI maintenance plan (for a variety of databases, applications, and sizes).

The SQL Server community recommends this solution for running backups, integrity checks, and index and statistics maintenance plan.

By the way, this check is a part of our SQL Server Health Check service.

Installing Ola Hallengren’s scripts

The installation of Ola Hallengren’s (OLA) should not be a problem, as the solution supports all Windows versions of SQL Server, starting from SQL Server 2008 (on all editions). It is also supported on Linux 2017, Azure SQL Database, and Azure SQL Database Managed Instance.

The installation has been described in Ola’s FAQ, but I will highlight the steps:

Download the sql script, and open it in SSMS.
In the script (around line 32), find this line “SET @BackupDirectory = NULL” and replace NULL with the path to your backup directory. For example: “SET @BackupDirectory = N’D:\Backup‘”. If you use a network share/UNC path, both SQL Server and SQL Server Agent services need to be run under a domain account with full control of the directory.
Set the integer value for the CleanupTime. In the script, find this line:
SET @CleanupTime = NULL. This value specifies how long (in hours) will each backup file be kept on the drive. The backup files will never be deleted automatically If the default NULL value is left in the script.
Before executing the MaintenanceSolution.sql script, we recommend changing the installation database from the master database to a “Tools” database. For this, you just need to change the line “USE [master]” to point to the database in which the objects will be created. The jobs will automatically be configured to run against this database. It also creates a few SQL Server Agent jobs that will be discussed separately.
Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Also, verify that the backup files are being made. Check the output files in the error log directory.
Schedule the SQL Agent jobs.

Another way to install the solution is by using the dbatools.io. The PowerShell CmdLet Install-DbaMaintenanceSolution installs the maintenance solution in a given database.

The maintenance components

Figure 1 – Ola hallengren’s Jobs (Categories).

SQL Server Backup

The following four database backup tasks use Ola’s stored procedure DatabaseBackup:

DatabaseBackup – SYSTEM_DATABASES – FULL: Performs the full backup of the three system databases: master, model and msdb.
DatabaseBackup – USER_DATABASES – DIFF: Make the differential backup of all user databases.
DatabaseBackup – USER_DATABASES – FULL: It performs the full backup of all user databases.
DatabaseBackup – USER_DATABASES – LOG: Make all user databases’ transactional backup using the Full recovery model.

How to backup a SQL database to an Azure storage account?

Create a storage account with block blob support (general-purpose v2 accounts in most cases).
Generate a SAS token that allows full read/write access to the container and copy the URL. Should look like this:
HTTPS://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/?sv=<TOKEN>

Figure 2 – Generating the SAS token.

Store the SAS credentials in SQL Server. To do this, in SSMS, run the following TSQL script (Replacing the YOUSTORAGEACCOUNTNAME string):

IF NOT EXISTS  (SELECT * FROM sys.credentials

             WHERE name = 'https://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/')

CREATE CREDENTIAL [https://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

```
SECRET = '';
```

Alter the @Url parameter in the SQL Agent backup Jobs. The key to this working is that the Url must match the SQL Server credential name created above. The agent job command job should look like:

```
EXECUTE [dbo].[DatabaseBackup]
```
```
@Databases = 'USER_DATABASES',
```
```
@BackupType = 'FULL',
```
```
@Compress='Y',
```

@Url='https://YOUSTORAGEACCOUNTNAME.blob.core.windows.net/',

```
@BlockSize=65536,
```
```
@MaxTransferSize=4194304
```

SQL Server Integrity Check

The following two database integrity jobs use the stored procedure DatabaseIntegrityCheck:

DatabaseIntegrityCheck – SYSTEM_DATABASES: This task performs an integrity check of all user databases.
DatabaseIntegrityCheck – USER_DATABASES: It performs an integrity check of all user databases.

SQL Server Index and Statistics Maintenance

The agent job below, created by the installation script, uses the IndexOptimize stored procedure. Many parameters can be used to configure the behavior of the process. To get full details on the procedure parameters, see this documentation page.

IndexOptimize – USER_DATABASES This task performs index optimization of all user databases.

Housekeeping tasks

There are four cleanup tasks.

CommandLog Cleanup: By default, the task deletes records older than 30 days logged into the table dbo.CommandLog.
Output File Cleanup: It deletes files named *_*_*_*.txt in the SQL Server log directory older than 30 days. This task uses the tokens ESCAPE_SQUOTE and SQLLOGDIR, which are only available within SQL Server agent job steps.
sp_delete_backuphistory: This task deletes backup history records in the msdb database for older than 30 days. An overloaded MSDB Backup History backup can severely affect the backup performance in older versions of SQL Server.
sp_purge_jobhistory: Deletes SQL Server agent job history records in the msdb database for older than 30 days.

Scheduling Strategy

How should I schedule jobs?

It depends on many factors like your maintenance window, your databases’ size, on predefined RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Here are some guidelines that you can start with:

User databases:

Full backup one day per week
Differential backup all other days of the week
Transaction log backup every hour
Integrity check one day per week
Index maintenance one day per week

System databases:

Full backup every day
Integrity check one day per week

I recommend that you run a full backup after the index maintenance. The subsequent differential backups will then be small. I also recommend that you perform the full backup after the integrity check. Then you know that the integrity of the backup is OK.

Cleanup:

sp_delete_backuphistory one day per week
sp_purge_jobhistory one day per week
CommandLog cleanup one day per week
Output file cleanup one day per week

To define a schedule for any of the jobs, perform the following steps:

In Object Explorer, expand SQL Server Agent and Jobs nodes.
Right-click on the backup job to open the context menu, and click on Properties.
In the Job Properties window, select the Schedules tab, and click on the New button to create a new schedule.
Provide the schedule name, and set the schedule’s type and frequency. Optionally, select the start and end dates for the plan. Click OK to save changes.
Figure 3 – Agent Job schedule options.
Click the OK button to create the schedule. The job will run automatically on the specified schedule from now on.

Monitoring

Automation is excellent but must always be monitored.

Using the parameter @LogToTable = ‘Y’ when running the OLA commands, it is easy to see if any errors occurred by inspecting the CommandLog table. You can use the script below to check:

SELECT ID, DatabaseName, CommandType, StartTime, EndTime, ErrorNumber

```
FROM Tools.dbo.CommandLog 
```
```
WHERE ErrorNumber &gt; 0
```

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

One Response

Nitti says:

April 19, 2023 at 9:54 AM

Mark, thank you for posting this page, it is very useful and informative. There was something that I read and have a question on, you posted:
“I recommend that you run a full backup after the index maintenance. The subsequent differential backups will then be small.”
From what i understand on Index maintenance it is done in one long transaction so the result is the transaction log can grow fairly large and even if you run a full backup after Index maintenance and the transaction log is cleared out you are left with all that free space in the log. Is this correct? if so how does one deal with that? I’ve read that shrinking files is a bad thing.

Reply