Index Optimize @Databases = 'USER_DATABASES', @Fragmentation Low = NULL, @Fragmentation Medium = 'INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @Fragmentation High = 'INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @Fragmentation Level1 = 5, @Fragmentation Level2 = 30, @Wait At Low Priority Max Duration = 1, @Wait At Low Priority Abort After Wait = 'NONE' Partitions will be rebuilt online by default.

updating sql proceedure-85updating sql proceedure-55updating sql proceedure-46

In the Index Optimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Index Optimize @Databases = 'USER_DATABASES', @Fragmentation Low = NULL, @Fragmentation Medium = 'INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @Fragmentation High = 'INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @Fragmentation Level1 = 5, @Fragmentation Level2 = 30 In this example, indexes that have a high fragmentation level will be rebuilt, online if possible.

Indexes that have a medium fragmentation level will be reorganized.

Indexes that have a low fragmentation level will remain untouched.

The Index Optimize procedure can also be used to update statistics.

You can use the Database Backup procedure’s @Change Backup Type option to change the backup type dynamically if a differential or transaction log backup cannot be performed.

Here’s an example of how to use the @Change Backup Type option: EXECUTE dbo.

Database Backup @Availability Groups = 'ALL_AVAILABILITY_GROUPS, -AG1', @Directory = 'C:\Backup', @Backup Type = 'FULL' Now what if you want to select all user databases that are not in availability groups?

For this scenario I have added a new keyword in the @Databases parameter. Database Backup @Databases = 'USER_DATABASES, -AVAILABILITY_GROUP_DATABASES', @Directory = 'C:\Backup', @Backup Type = 'FULL' It works the same way in Database Backup, Database Integrity Check, and Index Optimize.

Download, read, understand and implement these scripts.