Frequently Asked Questions

Getting Started

How do I get started with the SQL Server Maintenance Solution?

Getting started with the SQL Server Maintenance Solution is easy. Follow these steps.

1. Download MaintenanceSolution.sql.
2. In the script, find this line:
   SET @BackupDirectory = NULL
   and replace NULL with the path to your backup directory.
3. In the script, find this line:
   SET @CleanupTime = NULL
   and replace NULL with your cleanup time. The cleanup time is the number of hours after which the backup files are deleted.
4. Execute MaintenanceSolution.sql. This script creates all the objects and jobs that you need.
5. Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created. Verify that these jobs are completing successfully. Verify that the backup files are being created. Check the output files in the error log directory.
6. Schedule the jobs.

Do the objects need to be created in the master database?

The objects can be created in any database: master, msdb, or a utility database for database administrative scripts. It should be a standalone database. That is a database that is not in an availability group or mirrored.

You need to change this line of code in MaintenanceSolution.sql:

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.

Which versions of SQL Server are supported?

The SQL Server Maintenance Solution is supported on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.

Do you have a version of the SQL Server Maintenance Solution that runs on SQL Server 2005?

You can download the last version of the SQL Server Maintenance Solution that runs on SQL Server 2005 here.

Which editions of SQL Server are supported?

The SQL Server Maintenance Solution is supported on all editions of SQL Server.

Is any compatibility level required?

The database where you are creating the objects, has to be in compatibility level 90 or higher. All other databases can be in any compatibility level.

How can I check which version that is installed?

There is a version timestamp in the header of the stored procedures.

  ----------------------------------------------------------------------------------------------------
  --// Source:  https://ola.hallengren.com                                                        //--
  --// License: https://ola.hallengren.com/license.html                                           //--
  --// GitHub:  https://github.com/olahallengren/sql-server-maintenance-solution                  //--
  --// Version: 2018-06-24 15:24:40                                                               //--
  ----------------------------------------------------------------------------------------------------

Here is a script that you can use to check it, for all of the stored procedures.

How do I get started with the SQL Server Maintenance Solution on SQL Server Express?

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

1. Download MaintenanceSolution.sql.
2. Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
3. Create cmd files to execute the stored procedures; for example:
   sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
4. In Windows Scheduled Tasks, create tasks to call the cmd files.
5. Schedule the tasks.
6. Start the tasks and verify that they are completing successfully.

I’m getting this informational message when I create the stored procedure DatabaseBackup, DatabaseIntegrityCheck, or IndexOptimize:
“The module '' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.”
What should I do?

DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize are using the stored procedure CommandExecute. Download this object and create it in the same database.

You can also use the MaintenanceSolution.sql script, which creates all the objects that you need.

I’m using an old version of the SQL Server Maintenance Solution. How do I upgrade to the most recent version?

To upgrade to the most recent version of the SQL Server Maintenance Solution, follow these steps.

1. Download MaintenanceSolution.sql.
2. Change this line in the script so as not to create jobs:
   SET @CreateJobs = 'N'
3. Execute MaintenanceSolution.sql. The script drops the old versions of the stored procedures and creates new versions.
4. Start the jobs and verify that they are completing successfully.

SQL Server Backup

How do I back up to a network share?

• Verify that the SQL Server and the network share are in the same domain.
• Run the SQL Server service under a domain account.
• Run the SQL Server Agent service under a domain account.
• Grant the SQL Server and SQL Server Agent service accounts full control of the directory and the network share.
• If you are running the job under a proxy account, the proxy must be a domain account. Add the account to the sysadmin server role and grant it full control of the directory and the network share.
• Back up to a UNC path.

DatabaseBackup is not deleting old backup files. What could the problem be?

• Verify that the SQL Server and SQL Server Agent service accounts have full control of the backup directory.
• If that directory is a network share, verify that the SQL Server and SQL Server Agent service accounts have full control of the network share.
• If you are using a proxy account, verify that the account is a member of the sysadmin server role and that it has full control of the backup directory and network share.
• Verify that the file is not locked in the file system; for example, a backup or antivirus software could be locking the file.
• DatabaseBackup has been designed not to delete transaction log backups that are newer than the most recent full or differential backup. This could explain why transaction log backups are not being deleted.

How can I delete old backup files from Azure Blob Storage?

I have not been able to find a way to delete backup files in Azure Blob Storage from a T-SQL stored procedure. Because of that you cannot use the @CleanupTime parameter when you do backup to URL.

You will have to use a PowerShell script to delete old backup files in Azure Blob Storage. Here is one that you can use.

DatabaseBackup backs up to a directory with the SQL Server instance name, the database name, and the backup type in the directory path. Can I change this behavior?

DatabaseBackup has been designed to delete backup files only when the backup and verification were successful. Even then, DatabaseBackup deletes only backups of the same instance, database, and type. Therefore, you’re guaranteed to always have the most recent backup on disk. If you remove any of this information from the directory path, you no longer have this guarantee.

This is because of how xp_delete_file works: xp_delete_file is the extended stored procedure that DatabaseBackup (and the maintenance plans) use to delete backup files. xp_delete_file deletes backup files based on a directory, a file extension, and a modified date.

DatabaseBackup backs up to a file with the date and time in the filename. Can I change this behavior so that it always backs up to the same filename?

DatabaseBackup has been designed to delete backup files only when the backup and verification were successful. It backs up to a new filename every time. Therefore, you’re guaranteed to always have the most recent backup on disk. If you back up to the same filename, you no longer have this guarantee.

How should I configure DatabaseBackup to back up an Availability Group?

First you need to decide where you want the backups to be performed; on the primary replica or on the secondary replica. SQL Server has an option called backup preference that you can set on the availability group. By default it is set to Prefer Secondary, but you can change it to Primary if you prefer that.

Not all backup types are supported on the secondary replica. Here are the backup types that are supported:

• Copy-only full backups
• Log backups (non copy-only)

DatabaseBackup will perform these types of backups on the preferred backup replica. It is using the function sys.fn_hadr_backup_is_preferred_replica to determine if the current replica is the preferred replica.

Full backups (non copy-only) and differential backups are only supported on the primary replica. DatabaseBackup will always perform these types of backups on the primary replica.

Here are some different backup strategies that you can use:

• Perform all the backups on the primary replica. With this strategy it works just like with any database. You can perform full (non copy-only), differential, and log backups. All you need to do is to set the backup preference to Primary for the availability group.
  
  
• Perform all the backups on the secondary replica. With this strategy you can only perform copy-only full, and log backups (non copy-only). You need to check that the backup preference is set to Prefer Secondary for the availability group. You also need to add @CopyOnly = 'Y' to the full backup jobs, and disable the differential backup jobs.
  
  
• Perform full (non copy-only) and differential backups on the primary replica, and log backups on the secondary replica. You need to check that the backup preference is set to Prefer Secondary for the availability group. You don't need to add any additional parameters to the full, differential, or log backup jobs.

The jobs should be configured identically and be enabled and scheduled on all the replicas.

SQL Server Integrity Check

How should I configure DatabaseIntegrityCheck to check an Availability Group?

By default DatabaseIntegrityCheck is performing the checks on all replicas. This is the best practise as the primary and secondary replicas have different I/O subsystems.

Paul Randal is writing about this here.

“The lack of corruptions on the mirror‘s I/O subsystem implies nothing about the health state of the principal‘s I/O subsystem.”

If you need to control which replicas that are checked, you can use the parameter @AvailabilityGroupReplicas.

The jobs should be configured identically and be enabled and scheduled on all the replicas.

SQL Server Index and Statistics Maintenance

How should I configure IndexOptimize for an Availability Group?

Indexes can only be rebuilt or reorganized and statistics only updated on the primary replica in an availability group. IndexOptimize has a check for this.

The jobs should be configured identically and be enabled and scheduled on all the replicas.

Why are no indexes reorganized or rebuilt when I execute IndexOptimize?

The issue might be that the indexes are too small or not fragmented enough.

By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.

By default, IndexOptimize does not reorganize or rebuild indexes with a fragmentation of less than 5 percent. If you want to change this setting, you can use the @FragmentationLevel1 parameter.

Why are some of my indexes still fragmented, when I just executed IndexOptimize?

Small indexes sometimes show a high fragmentation even immediately after they’ve been reorganized or rebuilt. This is because the pages are stored on mixed extents. You can read about this situation in Books Online:

“In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.”

In addition, fragmentation on very small tables does not affect performance. Microsoft has a white paper about this:

“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”

By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.

Finally IndexOptimize is only working with indexes, not with heaps (index_id=0).

What should the fragmentation levels be for reorganizing and rebuilding indexes?

Microsoft offers some recommendations in Books Online. The default values in IndexOptimize are based on these recommendations.

Is there a script that I can use to check the index fragmentation?

Here's a script that you can use to check the index fragmentation.

How can I log the commands in IndexOptimize to a table so that I can analyze which indexes are becoming fragmented quickly?

You can use the @LogToTable = 'Y' option to log the commands to a table. Then you can use this script to analyze the data.

The transaction log is growing very large when I run the IndexOptimize job. What should I do?

Make sure that the transaction log backup job is running as it should.

Check that the transaction log has the storage that it needs. You should not shrink the transaction log file. Doing so costs resources to shrink and later to regrow the file.

Job Execution

Should I use CmdExec job steps with sqlcmd, or T-SQL job steps?

There is a problem in SQL Server 2005, 2008 and 2008 R2 that a T-SQL job step stops executing after the first error. The workaround to that is to use a CmdExec job step with sqlcmd and the -b option.

T-SQL job steps works well in SQL Server 2012 and later versions.

You can use the MaintenanceSolution.sql script to create the jobs. It will create CmdExec job steps with sqlcmd on SQL Server 2005, 2008 and 2008 R2, and T-SQL job steps on later versions.

The job stops executing after the first error. What could the reason be?

There could be multiple reasons:

• The stored procedure will stop at the first error, if you are executing it in a T-SQL job step in SQL Server 2008 R2 or earlier. If you are on SQL Server 2008 R2 or earlier, I recommend that you execute the stored procedures in a CmdExec job step with sqlcmd and the -b option.
• The stored procedure will stop at the first error, if you are executing it from a TRY block in a TRY...CATCH construct. I recommend that you do not execute the stored procedures from a TRY block.

Can I change the default parameter values for the stored procedures to suit our environment?

I recommend that you do not change the default parameter values for the stored procedures. Doing so makes it more difficult for you to upgrade to new versions of the stored procedures. Instead, I recommend that you pass your preferred values as parameters to the stored procedures in the jobs.

Should I pass the parameters to the stored procedures by name or by position?

I recommend that you pass the parameters to the stored procedures by name. I will always keep the names of the parameters the same in future versions of the stored procedures.

One of my jobs failed. I have checked View History, but I can’t see the reason for the failure.

View History shows only a small part of the output from the stored procedure—and it isn’t always the part where the error is. Therefore, you need to check the output files, which are in the same directory as the SQL Server error logs.

If you haven’t configured output files, you can use the MaintenanceSolution.sql script to create the jobs. The output files will be configured for you.

What is the purpose of using SQL Server Agent tokens?

Using a SQL Server Agent token for the server name in the sqlcmd command ensures that you execute the script against the local SQL Server.

Using SQL Server Agent tokens for jobid, stepid, date, and time in the output filenames ensures that those filenames are unique.

Using a SQL Server Agent token for the log directory in the output file path ensures that you can use the jobs in a multiserver environment.

You can use the MaintenanceSolution.sql script to create the jobs. Then the sqlcmd command and the output files with tokens will be configured for you.

I have a job with multiple steps. How can I configure the job to run all steps and report failure if any steps fail?

Unfortunately, this task is not as easy as it might seem. If you want to run all steps, you must configure all but the last step to [Go to the next step] on success and on failure, in case a step fails. The last step must be configured to [Quit the job reporting success] on success and [Quit the job reporting failure] on failure. The problem is that in this scenario, the job reports success whenever the last step is successful, even if one or more of the previous steps failed.

As a workaround, you could add a step at the end of the job, to determine whether any previous steps failed.

I want to receive an email notification when a job fails. How can I achieve this?

SQL Server Agent has built-in support for sending mails when a job fails. You can read about SQL Server Agent Mail in Books Online.

Can I schedule the execution of the stored procedures by using a job scheduler other than SQL Server Agent?

You can schedule the execution of the stored procedures by using any job scheduler.

Scheduling

How should I schedule jobs?

The answer depends on your maintenance window, the size of your databases, the maximum data loss you can tolerate, and many other factors. Here are some guidelines 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

I recommend that you run a full backup after the index maintenance. The following 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 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

Logging

I’m using the option to log to a table. Should I still log to output files?

You should always log to output files, which ensure that you have complete information in case of an error.

You can use the MaintenanceSolution.sql script to create the jobs. The output files will then be configured for you.

I’m using the option to log to a table. If there is an error in the BACKUP DATABASE command, then only one error is logged to the table, even though the command outputs two errors. Why is this?

Some commands in SQL Server output two errors. Unfortunately, you can capture only the last error.

You should always log to output files, which ensure that you have complete information in case of an error.

You can use the MaintenanceSolution.sql script to create the jobs. The output files will then be configured for you.

I’m using the option to log to a table. If there is an error in the BACKUP DATABASE command, then the error number is logged, but not the error message. Why?

To capture an error message in SQL Server, you need to use TRY CATCH and the ERROR_MESSAGE() function. Unfortunately TRY CATCH does not work well with the BACKUP DATABASE command, which outputs two errors (you can capture only the final one). To ensure that all error information is being output, I do not use TRY CATCH for the BACKUP DATABASE command in DatabaseBackup. Therefore the error message is not logged to the table.

You should always log to output files, which ensure that you have complete information in case of an error.

You can use the MaintenanceSolution.sql script to create the jobs. The output files will then be configured for you.

Security

Does the SQL Server Maintenance Solution use xp_cmdshell?

The SQL Server Maintenance Solution does not use xp_cmdshell.

Which permissions are needed for the SQL Server Maintenance Solution to work?

If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

• DatabaseBackup: sysadmin
• DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases
• IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

Deployment

I have a very large SQL Server environment. How can I deploy the SQL Server Maintenance Solution across all my servers?

In SQL Server Management Studio, you can execute scripts against multiple servers in parallel. This is an easy way to deploy the SQL Server Maintenance Solution across all your servers. You can also use a Central Management Server to make a central list of your servers.

To verify that you have the same version of the SQL Server Maintenance Solution on all your servers, you can check the version timestamps and checksums. Here is a script that you can use.

Uninstall

How do I uninstall the SQL Server Maintenance Solution?

Here is a script that you can use to uninstall the SQL Server Maintenance Solution.

Cleanup

What do sp_delete_backuphistory and sp_purge_jobhistory do?

You can read about sp_delete_backuphistory and sp_purge_jobhistory in Books Online.

High Availability

Does the SQL Server Maintenance Solution support Always On Availability Groups?

The SQL Server Maintenance Solution supports AlwaysOn Availability Groups.

Copy-only full backups and transaction log backups are performed on the preferred replica. Full backups (non copy-only) and differential backups are performed on the primary replica.

Integrity checks are performed on all replicas. Index and statistics maintenance is performed on the primary replica.

You can select availability groups using the @AvailabilityGroups parameter.

Does the SQL Server Maintenance Solution support database mirroring?

The SQL Server Maintenance Solution supports database mirroring. Backup, integrity check, and index and statistics maintenance are performed on the principal database.

Does the SQL Server Maintenance Solution support log shipping?

The SQL Server Maintenance Solution supports log shipping. Full and differential backup, integrity check, and index and statistics maintenance are performed on the primary database. Transaction log backups are not performed on databases that are involved in log shipping.

Does the SQL Server Maintenance Solution support failover clustering?

The SQL Server Maintenance Solution supports failover clustering.

Cloud

Is the SQL Server Maintenance Solution supported on Azure Virtual Machines?

The SQL Server Maintenance Solution is supported on Azure Virtual Machines.

Is the SQL Server Maintenance Solution supported on Amazon EC2?

The SQL Server Maintenance Solution is supported on Amazon EC2.

Is the SQL Server Maintenance Solution supported on Amazon RDS for SQL Server?

The integrity check and the index and statistics maintenance parts of the SQL Server Maintenance Solution is supported on Amazon RDS for SQL Server. Backup is performed by the automated backup feature of Amazon RDS.

Is the SQL Server Maintenance Solution supported on Azure SQL Database?

The integrity check and the index and statistics maintenance parts of the SQL Server Maintenance Solution is supported on Azure SQL Database. Backup is performed by the automated backup feature of Azure SQL Database.

You cannot use MaintenanceSolution.sql on Azure SQL Database. You need to download the objects as separate scripts.

Is the SQL Server Maintenance Solution supported on Azure SQL Database Managed Instance?

The SQL Server Maintenance Solution is supported on Azure SQL Database Managed Instance.

Does the SQL Server Maintenance Solution support backup to Azure Blob Storage?

The SQL Server Maintenance Solution supports backup to Azure Blob Storage.

General Questions

Isn’t it easier just to use the SQL Server maintenance plans?

The SQL Server maintenance plans are good in some cases but don’t always provide what you need. The SQL Server Maintenance Solution is reliable, easily deployable, has extensive logging, and has the features that are often needed in an enterprise environment.

How much does the SQL Server Maintenance Solution cost?

The SQL Server Maintenance Solution is free. You can read the license here.

Is the SQL Server Maintenance Solution available on GitHub?

The SQL Server Maintenance Solution is available on GitHub.

How can I be alerted about updates to the SQL Server Maintenance Solution?

You can sign up for the newsletter to be alerted about updates to the SQL Server Maintenance Solution.