SQL Server Maintenance Solution

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold winner in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.

“Hands down, one of the best tools out there! If you’re responsible for a database, you should be using Ola’s scripts. It’s very comprehensive, is efficient with resources, has numerous options, and is a brilliant piece of SQL coding!” - SQL Server Magazine

Getting Started

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.

Learn more about using the SQL Server Maintenance Solution:

Sign up for the newsletter to be alerted about updates to the solution.

SQL Server 2014

I have been testing the SQL Server Maintenance Solution on SQL Server 2014 RTM. I have not run into any issues so that is now supported. I have added support for backup encryption, lock priorities for online index rebuilds, online index rebuilds of partitions, and updating of statistics on memory-optimized tables.

Here is how you can back up a database using encryption:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

Here is how you can rebuild indexes online using low priority locks:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@WaitAtLowPriorityMaxDuration = 1,
@WaitAtLowPriorityAbortAfterWait = 'NONE'

Partitions will be rebuilt online by default.

Intelligent Index Maintenance

The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Take a look at this code:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

In this example, indexes that have a high fragmentation level will be rebuilt, online if possible. Indexes that have a medium fragmentation level will be reorganized. Indexes that have a low fragmentation level will remain untouched.

Update Statistics

The IndexOptimize procedure can also be used to update statistics. You can choose to update all statistics, statistics on indexes only, or statistics on columns only. You can also choose to update the statistics only if any rows have been modified since the most recent statistics update.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Solve “No Current Database” Issues

Most DBAs have experienced the error message “BACKUP LOG cannot be performed because there is no current database backup” or “Cannot perform a differential backup for database, because a current database backup does not exist”. These errors usually occur when you have created a new database or changed the database recovery model from Simple to Full. The answer is to determine, before you run the backup, whether a differential or transaction log backup can be performed. You can use the DatabaseBackup procedure’s @ChangeBackupType option to change the backup type dynamically if a differential or transaction log backup cannot be performed.

Here’s an example of how to use the @ChangeBackupType option:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@Verify = 'Y',
@ChangeBackupType = 'Y',
@CleanupTime = 24

Back up to Multiple Files

Databases are becoming larger and larger. You can tune the performance of SQL Server backup compression, by using multiple backup files, and the BUFFERCOUNT and MAXTRANSFERSIZE options. The DatabaseBackup procedure supports these options:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup, D:\Backup, E:\Backup, F:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 4,
@CleanupTime = 24

Run Integrity Checks of Very Large Databases

The SQL Server Maintenance Solution has been designed to do integrity checks of very large databases. In the DatabaseIntegrityCheck procedure you can choose do the checks on the database level, the filegroup level, or the table level. It also supports limiting the checks to the physical structures of the database:

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

SQL Server Community Awards

For the past four years, the SQL Server Maintenance Solution has been voted as Best Free Tool in the SQL Server Magazine Awards:

The Solution in the News and on the Web

I hosted a session about the SQL Server Maintenance Solution on SQLBits IX, in October 2011.

SQL PASS had a session about the SQL Server Maintenance Solution during the 24 Hours of PASS 2010 online conference.

SQL Server Magazine featured an article about the SQL Server Maintenance Solution in its November 2008 issue (PDF version).

SimpleTalk featured an article about the SQL Server Maintenance Solution in July 2010.

Feel free to contact me if you have any questions. Thank you for trying out the solution.