SQL Server Maintenance Solution

Menu

GitHub

GitHub

Awards

2011 SQL Server Magazine Community Choice 2011 SQL Server Magazine Editors' Best Microsoft Most Valuable Professional

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 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022. The solution is based on stored procedures. The solution has been designed for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution has been voted as Best Free Tool in the 2013, 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.

“The best starting point for building your own maintenance plan is the comprehensive and free script from Ola Hallengren. That’s what I recommend to my clients.” - Paul S. Randal

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:

The SQL Server Maintenance Solution is available on GitHub.

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

Backup to S3 storage

Are you doing backup to S3 storage? I have added support natively in DatabaseBackup.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@URL = 's3://myaccount.s3.us-east-1.amazonaws.com/mybucket',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@MaxTransferSize = 20971520,
@BackupOptions = '{"s3": {"region":"us-east-1"}}'

SQL Server Smart Backup to Multiple Files

Are you backing up to multiple files to improve backup performance? Are you running into the size limitations, when backing up to Azure Blob Storage?

I have had the option to backup to to multiple files for a long time. The challange has just been that you have got the same number of backup files for all databases, small or large.

I am now introducing two new options to do backup to multiple files.

Here's how it can be used to back up databases, with a size of 10240 MB or larger, to 8 files, and smaller databases to one file.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@CheckSum = 'Y',
@NumberOfFiles = 8,
@MinBackupSizeForMultipleFiles = 10240

Here's how it can be used to dynamically calculate the number of backup files, so that each file is getting at maximum 10240 MB.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@CheckSum = 'Y',
@MaxFileSize = 10240

Incremental Statistics

Are you using incremental statistics? I have a new version of IndexOptimize with support for incremental statistics. Here's how it works:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

The stored procedure will check sys.dm_db_incremental_stats_properties for each partition.

Online Resumable Index Rebuilds

Microsoft introduced online resumable index rebuilds in SQL Server 2017. This feature let's you resume an index rebuild, if it would get aborted. Here's to use it:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@Resumable = 'Y'

SQL Server Smart Differential and Transaction Log Backup

Microsoft has introduced support in DMVs for checking how much of a database that has been modified since the last (non copy-only) full backup, and how much transaction log that has been generated since the last log backup.

I am now utilizing this in DatabaseBackup, to perform smart differential and transaction log backups.

Here's how it can be used to perform a differential backup if less than 50% of the database has been modified, and a full backup if 50% or more of the database has been modified.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'DIFF',
@ChangeBackupType = 'Y',
@ModificationLevel = 50

Here's how it can be used to perform a transaction log backup if 1 GB of log has been generated since the last log backup, or if it has not been backed up for 300 seconds. This enables you to do more frequent log backups of databases with high activity, and in periods of high activity.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@LogSizeSinceLastLogBackup = 1024,
@TimeSinceLastLogBackup = 300

SQL Server Backup on Linux

Backup on SQL Server 2017 on Linux is now supported. Here's how to use it:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '/var/opt/mssql/backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

Striping of backups to Azure Blob Storage

The SQLCAT - team has a blog post about how you can optimize performance when backing up to Azure Blob Storage. You can stripe the backups to multiple files, and use the options MAXTRANSFERSIZE, and BLOCKSIZE.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@NumberOfFiles = 8,
@MaxTransferSize = 4194304,
@BlockSize = 65536

Working with Availability Groups

One of the most requested features for a long time has been the ability to select availability groups. I have added this now. Here is how it works:

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1, AG2',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'ALL_AVAILABILITY_GROUPS, -AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

Now what if you want to select all user databases that are not in availability groups? For this scenario I have added a new keyword in the @Databases parameter.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES, -AVAILABILITY_GROUP_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

It works the same way in DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize.

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 had a session about the SQL Server Maintenance Solution on PASS Summit in November 2014 (PDF version and demo scripts).

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

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

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

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

© 2024 Ola Hallengren