Ola Hallengren Maintenance for SQL Server [Full Guide]

If you are a database administrator (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 Ola Hallengren Maintenance Solution for running backups, integrity checks, and index and statistics maintenance plan.

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:

1. Download the SQL script, and open it in SQL Server Management Studio (SSMS).
2. In the script (around line 27), find this line DECLARE @BackupDirectory nvarchar(max) = NULL and replace NULL with the path to your backup directory.
   For example: DECLARE @BackupDirectory nvarchar(max) = 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.
3. 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.
4. 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.
5. 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.
6. Schedule the SQL Server Agent job.

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

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?

1. Create a storage account with block blob support (general-purpose v2 accounts in most cases).
2. 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>

3. Store the SAS credentials in the 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 = '';

4. 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 SQL Server Agent job command 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:

1. User databases:
   • Full backup one day per week
   • Differential backup on all other days of the week
   • Transaction log backup every hour
   • Integrity check one day per week
   • Index maintenance one day per week
2. 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.

3. 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:

1. In Object Explorer, expand SQL Server Agent and Jobs nodes.
2. Right-click on the Backup Job to open the context menu, and click on Properties.
3. In the Job Properties window, select the Schedules tab, and click on the New button to create a new schedule.
4. 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.

5. Click the OK button to create the schedule. The job will run automatically on the specified schedule from now on.

Monitoring

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

Automation is excellent but must always be monitored.