SQL Server Maintenance Solution

Version History

24 Mar 2014

Added support for updating of statistics on memory-optimized tables in SQL Server 2014.

22 Mar 2014

Added support for backup encryption in SQL Server 2014.

Added support for low priority lock wait options for online index rebuilds in SQL Server 2014.

19 Mar 2014

Added support for SQL Server 2014.

Changed to use the SQL Server Agent token SQLLOGDIR for output files.

Changed the @CleanupTime parameter in the backup jobs (new jobs only) in MaintenanceSolution.sql from 24 (24 hours) to NULL (no files are deleted).

18 Mar 2014

Fixed an issue with cleanup of backup files. If you were using availability groups, and did full and differential backups on the primary replica, and transaction log backups on the secondary replica, and there has been a failover, then cleanup of transaction log backup files was not working.

23 Feb 2014

Fixed an issue with DatabaseIntegrityCheck and IndexOptimize when running on Amazon RDS.

16 Feb 2014

Changed name of the parameter @EncryptionType to @EncryptionAlgorithm.

Changed name of encryption algorithms, from AES-128 to AES_128, and from AES-256 to AES_256.

Removed support for Red Gate SQL HyperBac.

12 Feb 2014

Fixed an issue with cleanup of backup files. If you were using availability groups, and did full and differential backups on the primary replica, and transaction log backups on the secondary replica, then cleanup of transaction log backup files was not working.

07 Feb 2014

Fixed an issue with rebuilding indexes on views and computed columns.

31 Jan 2014

Added support for online rebuild of partitions in SQL Server 2014.

Changed default for the @PartitionLevel parameter to 'Y'.

19 Jan 2014

Removed edition check for the @PartitionLevel parameter. On Standard Edition it will work the same as if it was Enterprise Edition, and no tables were partitioned.

23 Dec 2013

There is an issue in SQL Server with rebuilding an index online, if the index does not have ALLOW_PAGE_LOCKS enabled (kb article 2292737). The issue is that if you use multiple CPUs the index will become more fragmented, when you rebuild it. If IndexOptimize runs into this situation it changes the max degree of parallelism to 1 for that index. I have changed the workaround so that it applies to all versions.

22 Sep 2013

Optimized the cleanup of backup files.

12 Sep 2013

Improved handling for an issue in which a database exceeded the maximum length for a backup device.

02 Sep 2013

Added logging of backup preference for availability groups.

15 Jun 2013

Changed to use the SERVERPROPERTY to get the SQL Server error log directory.

09 Jun 2013

Added support for overriding the backup preference for availability groups.

23 May 2013

Fixed an issue with disabled columnstore indexes in IndexOptimize.

19 May 2013

Added support for indexes and statistics on objects that are created by internal SQL Server components.

Optimized performance in DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize when selecting large number of databases.

30 Apr 2013

Added support for setting the LOCK_TIMEOUT in DatabaseIntegrityCheck and IndexOptimize. The LOCK_TIMEOUT is not set by default.

17 Mar 2013

Added support for setting the maximum transfer size in Red Gate SQL Backup.

02 Mar 2013

Fixed an issue in which IndexOptimize did not return the correct return code.

17 Feb 2013

Optimized performance in DatabaseBackup for servers with a large number of databases.

04 Feb 2013

Changed to use path in sys.dm_os_server_diagnostics_log_configurations to get the SQL Server error log directory.

12 Jan 2013

Changed error messages in DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize.

08 Jan 2013

Moved the code that selects databases into the stored procedures DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize.

05 Jan 2013

Fixed an issue in which transaction log backups were not deleted when the last full or differential backup was a copy_only backup.

28 Dec 2012

Changed IndexOptimize to check modification_counter in sys.dm_db_stats_properties, to determine whether any rows have been modified since the most recent statistics update.

26 Nov 2012

Changed the behavior in DatabaseBackup so that databases that are setup for log shipping are skipped when you perform log backups.

28 Oct 2012

Fixed a locking issue with IndexOptimize when running multiple sessions in parallel.

04 Sep 2012

Fixed an issue with backup compression in SQL Server 2012 Business Intelligence Edition.

01 Sep 2012

Fixed an issue with performing a differential backup of a database with no full backup in SQL Server 2005.

29 Jul 2012

Added a check for columnstore indexes in IndexOptimize.

08 Jul 2012

Added support for striping of backups across multiple drives.

01 Jul 2012

Added support for check of filegroups in DatabaseIntegrityCheck.

Added support for check of tables and indexed views in DatabaseIntegrityCheck.

Added support for check of disk space allocation structures in DatabaseIntegrityCheck.

Added support for check of catalog consistency in DatabaseIntegrityCheck.

30 Jun 2012

Changed the maximum compression level for LiteSpeed from 10 to 8.

22 Jun 2012

Added support for the READ_WRITE_FILEGROUPS option in DatabaseBackup.

17 Jun 2012

Fixed an issue with a deadlock that occurred when querying the catalog view sys.database_recovery_status in SQL Server 2012.

20 May 2012

Changed the way backup of availability groups works. Full (non copy-only) and differential backups are performed on the primary replica. Full copy-only backups and transaction log backups are performed on the preferred replica.

Added logging of availability groups and availability group roles.

Added cluster name and availability group name to the backup directory and filename, for databases in availability groups.

14 Apr 2012

Added support for backing up preferred replicas in SQL Server 2012 AlwaysOn Availability Groups.

10 Mar 2012

Added support for SQL Server 2012.

Added support for online rebuilding of indexes with varchar(max), nvarchar(max), varbinary(max), or XML data types or large CLR types in SQL Server 2012.

12 Feb 2012

Added support for deleting old output - files on international versions of Windows.

30 Dec 2011

Added support for specifying an output file directory in MaintenanceSolution.sql.

Added support for specifying @LogToTable in MaintenanceSolution.sql.

17 Dec 2011

Fixed an issue with the job that deletes old output files.

26 Nov 2011

Fixed an issue that occurred when deleting encrypted Red Gate SQL Backup backup files.

23 Oct 2011

Added support for backup encryption that uses LiteSpeed for SQL Server, Red Gate SQL Backup and SQL HyperBac, or Idera SQL safe backup.

17 Oct 2011

Fixed an issue with rebuilding indexes on views of databases in compatibility level 80.

04 Sep 2011

Improved the checking of output filenames when creating jobs.

24 Aug 2011

Improved the checking of the @LogToTable parameter.

21 Aug 2011

Changed the command type for transaction log backups to BACKUP_LOG.

11 Aug 2011

The table CommandLog is now created in MaintenanceSolution.sql.

Added a job for deleting old rows in CommandLog.

08 Aug 2011

Fixed an issue in which blank lines were not displayed when executing the stored procedures in SQL Server Management Studio.

04 Aug 2011

Added COPY_ONLY to backup directory and filename.

Added support for deleting old backup files when performing COPY_ONLY backups.

01 Aug 2011

Modified a download link to one of the backup-application vendors.

31 Jul 2011

Aligned the positions of the @FragmentationLow, @FragmentationMedium, and @FragmentationHigh parameters with the @FragmentationLevel1 and @FragmentationLevel2 parameters. If you are passing the parameters to the stored procedure by name (recommended), this change will not affect you.

30 Jul 2011

Added download links to error messages if you select a backup application that is not installed.

09 Jul 2011

Support for logging commands to a table by using the @LogToTable parameter.

18 Jun 2011

Jobs are now created under the [Database Maintenance] category.

15 May 2011

Optimized performance in IndexOptimize for databases with a large number of indexes.

04 Mar 2011

Added handling for an issue in which a database with a very long name exceeded the maximum length for a backup device.

Added support for the TABLOCK option in DatabaseIntegrityCheck.

20 Jan 2011

Added support for Idera SQL safe backup.

16 Jan 2011

Added support for a delay between index commands in IndexOptimize.

08 Jan 2011

Added @FragmentationHigh, @FragmentationMedium, and @FragmentationLow parameters for defining your preferred index maintenance.

Added the @UpdateStatistics parameter for updating statistics.

25 Dec 2010

Added support for setting the number of threads in LiteSpeed for SQL Server and Red Gate SQL Backup.

Added support for setting the maximum CPU usage in LiteSpeed for SQL Server.

09 Dec 2010

Added support for updating column statistics.

Added support for updating statistics only when the data has been modified. Set @OnlyModifiedStatistics = 'Y' to use this option.

Added support for updating statistics by using the RESAMPLE option. Set @StatisticsResample = 'Y' to use this option.

Added support for updating statistics using NORECOMPUTE on statistics that are disabled for auto-update.

Optimized performance in IndexOptimize. It now queries the DMV sys.dm_db_index_physical_stats only if needed.

12 Sep 2010

Added support for the PAD_INDEX option in IndexOptimize.

15 Aug 2010

Improved error handling in IndexOptimize, in situations when the query that selects indexes from the system tables is blocked.

03 Aug 2010

Added support for selecting schemas, objects, and indexes in IndexOptimize.

21 Jul 2010

Added support for creating a backup description.

20 Jul 2010

Added support for Red Gate SQL HyperBac backup compression.

Changed the file extension for Red Gate SQL Backup backups; the SQL Backup default file extension .sqb is now used.

Changed the behavior for COPY_ONLY backups; deleting old backup files when you’re performing a COPY_ONLY backup is no longer supported.

30 May 2010

Fixed a collation conflict that occurred when creating the objects in a database with a collation other than the one in tempdb.

23 May 2010

Changed the default for backup compression. The compression default in sys.configurations is now used.

16 May 2010

Added a job for purging job history, using the stored procedure sp_purge_jobhistory.

Added a job for purging backup history, using the stored procedure sp_delete_backuphistory.

Added a job for deleting output files.

15 May 2010

Added a workaround for an issue in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2, in which fragmentation sometimes remains in an index that has page locking disabled, when you use multiple CPUs to rebuild the index online. The workaround is in IndexOptimize setting MAXDOP = 1, when you rebuild an index that has page locking disabled online.

14 May 2010

DatabaseBackup now supports backup compression in SQL Server 2008 R2 Standard Edition.

26 Apr 2010

Added support for SQL Server 2008 R2.

05 Dec 2009

Added support for setting the compression level when you use LiteSpeed for SQL Server or Red Gate SQL Backup.

21 Nov 2009

Added support for backing up to multiple files.

Added support for Red Gate SQL Backup.

13 Nov 2009

Added support for the BLOCKSIZE option in DatabaseBackup.

Added support for the BUFFERCOUNT option in DatabaseBackup.

Added support for the MAXTRANSFERSIZE option in DatabaseBackup.

22 Aug 2009

Added support for selecting all databases: @Databases = 'ALL_DATABASES'.

Added support for databases in single-user mode.

Added a check in DatabaseBackup to determine whether database names are unique in the file system.

06 Aug 2009

Added the @TimeLimit parameter in IndexOptimize. Use this parameter to set the time in seconds, after which no commands are executed.

01 Aug 2009

Added support for the EXTENDED_LOGICAL_CHECKS option in DatabaseIntegrityCheck.

Added support for the CHECKSUM option in DatabaseBackup.

Added support for a default backup directory in DatabaseBackup.

Added a check for databases in standby mode in DatabaseBackup.

20 Jun 2009

Changed the behavior in DatabaseBackup so that when deleting old log backups, the procedure doesn't delete any log backups that are newer than the most recent full or differential backup.

06 Jun 2009

Added support for printing commands without executing them. Set the new @Execute parameter to 'N' to use this option.

21 May 2009

Added support for rebuilding and reorganizing partitioned indexes on the partition level. Set the new @PartitionLevel parameter to 'Y' to use this option.

10 May 2009

Added support for LiteSpeed for SQL Server backup.

Added the ability to exclude indexes on read-only file-groups in IndexOptimize.

16 Apr 2009

Added support for XML indexes.

Added support for spatial indexes.

Added support for indexed views.

Added a check for large CLR types in IndexOptimize.

Added a check for indexes with page locking disabled in IndexOptimize.

01 Mar 2009

Added support for the COMPRESSION option in DatabaseBackup.

Added support for the COPY_ONLY option in DatabaseBackup.

Added a check to determine whether a differential backup can be done, before performing a differential backup in DatabaseBackup.

Added a check to determine whether a transaction log backup can be done, before performing a transaction log backup in DatabaseBackup.

Added the default parameter value 'N' for backup verification in DatabaseBackup.

Added the default parameter value NULL for cleanup of old backups in DatabaseBackup.

Fixed an issue in DatabaseBackup in which non-Latin characters in the backup directory name generated an error.

Added support for the NOINDEX option in DatabaseIntegrityCheck.

Added support for the PHYSICAL_ONLY option in DatabaseIntegrityCheck.

DatabaseIntegrityCheck now uses the ALL_ERRORMSGS option to log all errors.

Added support for the MAXDOP option in IndexOptimize.

Added support for the SORT_IN_TEMPDB option in IndexOptimize.

Added support for the FILLFACTOR option in IndexOptimize.

Added support for the LOB_COMPACTION option in IndexOptimize.

Added support for the SAMPLE and FULLSCAN options in IndexOptimize.

Added support for the exclusion of disabled and hypothetical indexes in IndexOptimize.

Added support for the exclusion of read-only databases in IndexOptimize.

Added support for wild-cards in the database selection.

Added logging of duration for all commands.

Fixed an issue in which percent characters (%) in database, schema, table, or index names generated an error.

Fixed an issue in the installation script, in which SQL Server Agent check did not work in SQL Server Express.

02 Aug 2008

Added support for SQL Server 2008.

Added support for non-Latin characters in database, schema, object, and index names.

Added support for single quotes (') in schema, object, and index names.

Added support for case-sensitive databases.

Added support for characters in database names that are not allowed in the file system (\ / : * ? " < > |).

Added support for backing up to UNC paths.

Changed the behavior in DatabaseBackup so that databases that are in simple recovery model are skipped when you perform log backups.

Changed the behavior in DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize so that database snapshots are excluded.

Fixed an issue in IndexOptimize, in which a nonclustered index with a varchar(max), nvarchar(max), or varbinary(max) data type in an included column cannot be rebuilt online.

Improved error handling in IndexOptimize when the query that is checking the fragmentation is blocked.

Improved logging.

01 Jan 2008

This is the initial version of the SQL Server Maintenance Solution.