SQL Server Maintenance Solution

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 = N'C:\Backup'
    and replace C:\Backup with the path to your backup directory.
  3. Execute MaintenanceSolution.sql. This script creates all the objects and jobs that you need.
  4. 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.
  5. 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. You just 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 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.

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 SQL Server Maintenance Solution is supported on all compatibility levels.

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?

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

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.

What is the easiest way to perform a point-in-time restore, based on full, differential, and transaction log backups?

The new Database Recovery Advisor (Restore Database) in SQL Server Management Studio can generate a restore script for you, based on the backup files that you have and the time to which you want to restore. This solution also works if you are using an earlier version of SQL Server.

SQL Server Index and Statistics Maintenance

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 @PageCountLevel 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 @PageCountLevel parameter.

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

Do the stored procedures need to be executed in a CmdExec job step with sqlcmd? Or can I use a T-SQL job step?

The stored procedures must be executed in a CmdExec job step with sqlcmd and the -b option, if error handling and logging are to work as designed. The problem with the T-SQL job step is that it stops executing after the first error.

You could use the MaintenanceSolution.sql script to create the jobs.

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.

Why does my job stop after the first error?

This issue is happening because you’re using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then the job will continue after an error.

You can use the MaintenanceSolution.sql script to create the jobs.

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.

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 sometimes get this error message when I execute the stored procedure through sqlcmd:
“Sqlcmd: Error: Internal error at ExecuteSqlCmd (Reason: Unspecified error).”
What does it mean?

This is a bug in sqlcmd in SQL Server 2008 and SQL Server 2008 R2. Microsoft has an article that describes the bug and lists the service packs and cumulative update packages in which it has been fixed.

I’m getting the following error message when I execute jobs:
“The process could not be created for step 1 of job 0x8F685552FA37FB4DA247A6B16811075A (reason: The system cannot find the file specified).”
What should I do?

You are getting this error because there is a problem with the path to sqlcmd. Check the PATH environment variable.

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. Schedule the sqlcmd commands with the -b option.

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:

System databases:

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:

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.

The output files have [SQLSTATE 01000] or [SQLSTATE 42000] at the end of each line. Why is this?

This behavior occurs if you’re using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. This behavior will then go away.

You can use the MaintenanceSolution.sql script to create the jobs.

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:

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 take a checksum of the object definitions and verify that they are the same.

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 AlwaysOn Availability Groups?

The SQL Server Maintenance Solution supports AlwaysOn Availability Groups.

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 Windows Azure Virtual Machines?

The SQL Server Maintenance Solution is supported on Windows 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 Windows Azure SQL Database?

The SQL Server Maintenance Solution is not supported on Windows Azure SQL Database.

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.

I’m thinking about having a session about the SQL Server Maintenance Solution in my user group. Do you have some material that I can use?

You may use this slide deck and these demo scripts, which I used in the session that I conducted at SQLBits IX in October 2011.

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.