Database maintenance is a critical yet often overlooked aspect of SQL Server management. Without regular backups, index optimizations, and integrity checks, performance degrades, and the risk of data loss increases. Thankfully, Ola Hallengren’s SQL Server Maintenance Solution provides a powerful, free, and widely trusted framework to automate these essential tasks.
This article will walk you through setting up and configuring Ola Hallengren’s scripts for backups, index optimization, and integrity checks. Whether you are a DBA or a developer managing SQL Server databases, this solution simplifies maintenance while ensuring reliability and performance. Let’s dive in!
Figure-1: Ola Hallengren’s SQL Server Maintenance Solution
Ola Hallengren SQL Server Maintenance Solution
This solution was designed by Ola Hallengren, a database administrator and database developer, who has been working with SQL Server since 2001. The solution is designed for the most mission-critical environments, and it is used in many organizations around the world. It has been voted as Best Free Tool in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards.
It is a highly customizable. After installation on the SQL Server, this scripts create jobs and stored procedures which has three parts:
- Running Backup
- Integrity Check
- Index and Statistics Maintenance
Installation
This solution supports all editions of Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022.
- Download MaintenanceSolution.sql. This script creates all the required objects and jobs.
- Save in suitable location and open it in SSMS.
- Locate below lines and change the @BackupDirectory.
DECLARE @CreateJobs nvarchar(max) = 'Y' -- Specify whether jobs should be created.
DECLARE @BackupDirectory nvarchar(max) = N'C:\DBBackup' -- Specify the backup root directory. If no directory is specified, the default backup directory is used.
DECLARE @BackupURL nvarchar(max) = NULL -- Specify the backup root URL.
DECLARE @CleanupTime int = 72 -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
DECLARE @OutputFileDirectory nvarchar(max) = NULL -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
DECLARE @LogToTable nvarchar(max) = 'Y' -- Log commands to a table.
- Connect with your intended SQL Server's master database.
- Make sure that the SQL Server agent is installed and running.
- Execute the MaintenanceSolution.sql script and you will see below objects:
Figure-2: Table and stored procedures
Figure-3: SQL Agent Jobs
Key Installed Components
The solution scripts will create below objects:
- Table
- CommandLog: you will see the activity log here like executed command, command type, database name, start time, endtime and error message.
- Stored Procedure
- CommandExecute: it executes various T-SQL commands, save the start and end times for each command and command's final outcome.
- DatabaseBackup: it carries out all backup related tasks. Apart from default settings, it takes care of parameters like Verify, Compress, CheckSum.
- DatabaseIntegrityCheck: it is responsible for integrity check jobs. It can execute DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, DBCC CHECKALLOC, DBCC CHECKCATALOG etc.
- IndexOptimize: it is used by IndexOptimize job.
- SQL Server Agent jobs
- CommandLog Cleanup: this is agent job for cleaning the CommandLog table. You should schedule this to cleanup once in a week.
- DatabaseBackup – SYSTEM_DATABASES – FULL: this job take full database backup of system databases.
- DatabaseBackup – USER_DATABASES – DIFF: it takes differential backups of all user databases. You should backup at least one full database backup for user databases before running this job, otherwise it may fail.
- DatabaseBackup – USER_DATABASES – FULL: it takes full backups of all user databases.
- DatabaseBackup – USER_DATABASES – LOG: it backups the transaction log of all user databases which are using Full or Bulk-logged recovery models. Databases those are using Simple recovery model are skipped.
- DatabaseIntegrityCheck - SYSTEM_DATABASES: for default settings, it runs the DBCC CHECKDB command against all system databases.
- DatabaseIntegrityCheck - USER_DATABASES: for default settings, it runs the DBCC CHECKDB command against all user databases. This command scans for any potential traces of corruption on a database. If the job fails, you should determine and remove the source of corruption to prevent the potential data loss.
- IndexOptimize - USER_DATABASES: it rebuilds and reorganizes indexes and update statistics. There are parameters regarding Databases, Fragmentation, NumberOfPages, SortInTempdb, MaxDOP etc which will be cover in next sections.
- Output File Cleanup: it cleans output file.
- sp_delete_backuphistory: it cleans backup history.
- sp_purge_jobhistory: it clears purge job history.
Scheduling The Jobs
The created SQL Server Agent Jobs are not scheduled. You can schedule it as per your maintenance window, the size of your databases, the maximum data loss you can tolerate, and many other factors. Here are some guidelines from Ola that you can start with, but you will need to adjust these to your environment.
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
Ola recommends that you run a full backup after the index maintenance. The following differential backups will then be small and also perform the full backup after the integrity check. Then you know that the integrity of the backup is okay.
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
Customizing Ola's Scripts
The beauty of Ola Hallengren’s script is customization. You can easily tweak and automate (using SQL Server Agent Job) which fit your own environment. For index optimization, you can write below script. This will work on AdventureWorks2022 and StackOverflow databases.
EXECUTE IndexOptimize
@Databases = 'AdventureWorks2022, StackOverflow',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@TimeLimit = 3600
For details on index and statistics maintenance parameters and options please visit Ola's official site.
For integrity checking, run below script which will perform CHECKDB operation on AdventureWorks2022 and StackOverflow databases.
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'AdventureWorks2022, StackOverflow',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y',
@NoIndex = 'Y'
For details on database integrity check parameters and options please visit Ola's official site.
Last Words
Maintaining a SQL Server environment does not have to be complicated. With Ola’s SQL Server Maintenance Solution, you get a powerful, flexible, and battle-tested set of scripts that handle backups, integrity checks, and index maintenance with ease. Whether you are managing a small instance or a large-scale production environment, this solution helps you automate routine tasks and focus more on optimization and less on firefighting.
If you have not implemented it yet—now is the perfect time. Your SQL Server will thank you!
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.