SQL SERVER – 2005 2008 – Backup, Integrity Check and Index Optimization By Ola Hallengren

Script of Backup, Integrity Check and Index Optimization are the most important scripts for any developer. SQL Expert and true SQL enthusiast Ola Hallengren is known for his excellent scripts.

Please try it out and let me know what you think. The documentation is available on http://ola.hallengren.com/Documentation.html and the script can be downloaded from http://ola.hallengren.com.

Here is brief documentation sent by Ola himself for his script in his own words.

Backup Maintenance

I think that most of you have experienced the error messages “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

This usually happens when you have created a new database or when you have changed recovery model of a database from Simple to Full.

The consequence is that the database is not getting backed up, until a full backup (or a differential backup for the BACKUP LOG error message if that can be done) has been performed. It will also create some noise in your monitoring system.

The solution is to check if a differential or transaction log backup can be performed before doing the backup. This can be done by checking sys.master_files.differential_base_lsn and sys.database_recovery_status.last_log_backup_lsn.

The backup solution that I have developed has a parameter called @ChangeBackupType.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'LOG', @ChangeBackupType = 'Y'

If it’s a new database in Full recovery model, the backup type for that database and job run will be changed to full. If it’s a database that was newly changed to Full recovery model, then the backup type for that database and job run will be changed to differential. The next time the job runs a transaction log backup will be performed for that database.

New databases start getting backed up quickly and no more “BACKUP LOG cannot be performed because there is no current database backup.” and “Cannot perform a differential backup for database “”, because a current database backup does not exist.”.

Index Optimization

The design idea is categorize all indexes based on their fragmentation level (High, Medium or Low) and whether there are columns with LOB (Large Object) data types. For each category you can define an action. The possible actions are to rebuild indexes online or offline, reorganize indexes, update statistics, reorganize indexes and update statistics or to do nothing.

Here’s an example.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000


Indexes with a fragmentation above 30% are to be rebuilt, online if possible (no LOB columns), otherwise offline (LOB columns). Indexes with a fragmentation between 5% and 30% are to be reorganized. Indexes with a fragmentation below 5% or a size below 1000 pages are not to be touched.

If you are using partitioning IndexOptimize has a parameter, @PartitionLevel to do index rebuilds and reorganizations on the partition level. If you prefer to do sort operations in tempdb you can do that with the parameter @SortInTempdb and if you would like to set a fillfactor you can do that with the parameter @FillFactor.

Reference: Pinal Dave (https://blog.sqlauthority.com)