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 updates to the maintenance scripts:

  • 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