Ola Hallengren SQL Server Maintenance Solution: Backup, Integrity, and Index Maintenance

What This Solution Replaces and Who Needs It

SQL Server's built-in maintenance plan wizard creates plans through a GUI but offers limited control over backup compression settings, differential backup logic, integrity check scope, index fragmentation thresholds, and parallel index operations. The wizard also does not handle logging of command execution in a way that is easy to query or alert on.

Ola Hallengren's solution replaces the wizard with directly configurable TSQL stored procedures. Each procedure exposes dozens of parameters. DatabaseBackup controls compression, encryption, checksum validation, backup device paths, verification, and copy-only backups. DatabaseIntegrityCheck controls which checks to run (CHECKDB, CHECKALLOC, CHECKCATALOG, or individual tables), whether to repair, and which databases to include or exclude. IndexOptimize controls fragmentation thresholds for rebuild versus reorganize, statistics update logic, fill factor, and parallel execution.

The target users are DBAs who manage multiple SQL Server instances and need reproducible, auditable maintenance jobs that behave consistently across environments.

The Five Core Scripts and Their Dependencies

The repository contains seven scripts. CommandExecute.sql and CommandLog.sql form the foundation: CommandExecute is a stored procedure that executes and logs TSQL commands with error handling and retry logic. CommandLog is the table that stores execution results.

DatabaseBackup.sql, DatabaseIntegrityCheck.sql, and IndexOptimize.sql are the three main maintenance stored procedures. Each depends on CommandExecute and must be deployed after it. When updating any of these three procedures, the README states that CommandExecute should also be updated at the same time.

Queue.sql and QueueDatabase.sql support parallel processing of multiple databases by multiple SQL Agent job steps. They are required only for setups that run maintenance operations across many databases in parallel.

MaintenanceSolution.sql bundles all seven objects plus SQL Agent jobs into a single script for SQL Server 2017, 2019, 2022, and 2025 plus Azure SQL Managed Instance. MaintenanceSolutionAzureSQLDatabase.sql contains only the integrity check and index/statistics maintenance objects, since backup is handled differently on Azure SQL Database.

Deploying the Solution

The solution is deployed by downloading the appropriate SQL script and executing it against the target SQL Server instance. For on-premises or Azure SQL Managed Instance:

Download MaintenanceSolution.sql from the repository root, then execute it in SSMS or via sqlcmd against the database where you want the maintenance objects created. The script creates all stored procedures, tables, and SQL Agent jobs in one step.

For security-conscious deployments, the README includes SHA-256 checksums in SHA256SUMS.txt. Verify the downloaded script before executing:

Each script has a corresponding checksum entry. An individual component script can be deployed instead of the full solution if only one capability is needed. For example, DatabaseIntegrityCheck.sql plus CommandExecute.sql and CommandLog.sql deploys only the integrity check functionality.

The documentation for each stored procedure, including all parameters, examples, and expected output, is available on the project's website at ola.hallengren.com and in the docs/ directory within the repository.

Index Maintenance Logic: When to Rebuild versus Reorganize

IndexOptimize separates index maintenance into three operations: rebuild, reorganize, and statistics update. The procedure accepts fragmentation thresholds as parameters. Below one threshold, no action is taken. Between the two thresholds, the index is reorganized. Above the upper threshold, it is rebuilt.

The procedure queries index fragmentation using sys.dm_db_index_physical_stats and applies the operation appropriate for each index's current state. This is more precise than a blanket rebuild of all indexes on a schedule, which wastes time on low-fragmentation indexes and skips statistics updates on indexes that are too small to trigger the automatic statistics update threshold.

The statistics update logic in IndexOptimize goes beyond the default auto-update statistics behavior. It can update statistics on objects where the modification counter has crossed a threshold, which catches large tables that would otherwise not trigger automatic updates until a very large number of row modifications have accumulated.

Limitations: No Cross-Platform Support and No GUI

The solution is TSQL and runs only on SQL Server and its cloud variants. It does not support MySQL, PostgreSQL, or any other database engine. DBAs who manage mixed environments need separate tooling for non-SQL Server databases.

There is no graphical interface. Configuration happens entirely through stored procedure parameters passed when SQL Agent jobs call the procedures, or by modifying the job step commands after deployment. Teams who prefer GUI-based maintenance configuration should stay with SQL Server's built-in maintenance plan wizard, accepting its limitations.

The solution does not provide query store cleanup, log shipping configuration, or availability group synchronization health monitoring. Each of these requires separate tooling or custom SQL Agent jobs.

For Azure SQL Database, the backup-related objects are not included in MaintenanceSolutionAzureSQLDatabase.sql because Azure SQL Database manages its own backups automatically. Attempting to run DatabaseBackup against an Azure SQL Database is not supported.

Comparison with SQL Server's Built-in Maintenance Plans

SQL Server's built-in maintenance plans use SSIS under the hood and are created through the Management Studio wizard. They support the same fundamental operations (backup, integrity check, index rebuild), but configuration options are limited to what the wizard exposes. Logging goes to the standard SQL Server log, not to a queryable table.

The Hallengren solution differs by putting all configuration in stored procedure parameters, making every setting auditable through the job step command text. Execution history is stored in CommandLog, which can be queried like any other table. This makes it straightforward to report on backup completion times, integrity check failures, or index rebuild durations across all databases on a server.

Another common comparison is Minion Maintenance, which is also a TSQL-based solution with a similar scope. The Hallengren solution predates Minion Maintenance and has broader deployment, but both approaches solve the same core problem of parameter-driven, logged SQL Server maintenance.

Maintenance Status and License

The last push to the repository was on 2026-09-12. The project has no GitHub releases; updates are distributed through the scripts directly. Version history is documented on the project website at ola.hallengren.com/versions.html.

The license is MIT. There are no restrictions on deploying the solution commercially or in any organizational context.

The repository contains a .gitattributes file and a SHA256SUMS.txt that is updated with each change to any of the scripts. The docs/ directory contains Markdown documentation for each stored procedure, mirroring the website content.

Editorial conclusion

DBAs who manage SQL Server 2017 or later instances and find the built-in maintenance plan wizard too rigid should deploy MaintenanceSolution.sql first and then read the stored procedure documentation on ola.hallengren.com before adding any SQL Agent jobs. Teams running Azure SQL Database get a subset of the functionality through MaintenanceSolutionAzureSQLDatabase.sql. The SHA-256 checksums in SHA256SUMS.txt are the right starting point for any security-conscious deployment: verify the downloaded script matches the checksum before executing it against production.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. olahallengren/sql-server-maintenance-solution on GitHub
4. Project website
5. README