Backup, Integrity Check and Index Optimization

Ola Hallengren and his ongoing work of his SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization scripts - makes me happy :-)

He made some important updates to his maintenance scripts:

  • Support for selection of schemas, objects and indexes. You can select individual or lists of schemas, objects or indexes, use exclusions or use wild-cards. See the documentation for details about how it can be used.

It works like this:
All indexes on the Production.Product table in the AdventureWorks database.
@Indexes = 'AdventureWorks.Production.Product'

The index PK_Product_ProductID on the Production.Product table in the AdventureWorks database.
@Indexes = 'AdventureWorks.Production.Product.PK_Product_ProductID'

As you see the syntax is DatabaseName.SchemaName.ObjectName.IndexName.The IndexName is optional.

You can exclude tables or indexes like this.
@Indexes = 'ALL_INDEXES,-AdventureWorks.Production.Product'

You can use wild-cards like this.
@Indexes = 'AdventureWorks.Production.Prod%'

You can select multiple indexes like this.
@Indexes = 'AdventureWorks.Production.Product,AdventureWorks.Production.ProductModel'

  • Improved error handling in IndexOptimize, in the case when the query that is selecting indexes from the system tables is blocked.
  • Support for backup description.
  • If a VSS snapshot has been taken since the last full backup, a differential backup cannot be performed. In the previous version this generated an error. In the new version there is a check for this.
  • Support for HyperBac backup compression. Set @BackupSoftware = 'HYPERBAC' to use this option.
  • Changed file extension for SQLBackup backups. In the new version the SQLBackup default file extension .sqb is used.
  • Changed behaviour for COPY_ONLY backups. It is no longer supported to delete old backup files when you're doing a COPY_ONLY backup.
  • Changed behaviour in the job that deletes old output files. The old version returned an error if there was no files to delete. This has been changed not to return an error.
  • Bug fix: Collation conflict when creating the objects in a database with a collation other than the collation in tempdb.
  • Changed default for backup compression. In the new version the backup compression default in sys.configurations is used in SQL Server 2008 Enterprise Edition and in SQL Server 2008 R2 Standard, Enterprise and Datacenter Edition. In SQL Server versions and editions where backup compression is not supported the default is 'N'.
  • New job for purging job history. It's using the stored procedure sp_purge_jobhistory
  • New job for purging backup history. It's using the stored procedure sp_delete_backuphistory
  • New job for deleting output files
  • If you have an index with page locking disabled and you rebuild it online using multiple CPUs, the fragmentation will in some cases not go away. This is a bug in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. I have made a workaround in IndexOptimize setting MAXDOP = 1, when you rebuild an index online that has page locking disabled
  • Now backup compression is supported also in Standard Edition