Three-In-One Database Maintenance Solution

Maintaining databases is an important but time-consuming job. I wrote a script, MaintenanceSolution.sql, that lets you easily perform three common maintenance tasks: backing up databases, checking the integrity of databases, and optimizing indexes. The script is set up so that you can call its three main stored procedures— DatabaseBackup, IndexOptimize, and Database- IntegrityCheck—separately. Thus, you can perform each task independently on the databases you specify.

MaintenanceSolution.sql is not only easy to use but also offers features such as dynamic index optimization. Rebuilding and reorganizing indexes dynamically has a number of advantages. It lets you rebuild or reorganize only the indexes that need to be rebuilt or reorganized, so locking and blocking are minimized and fewer system resources are used. This, in turn, can result in higher availability and reduce the size of differential and transaction log backups.

MaintenanceSolution.sql works on the Standard, Enterprise, Workgroup, Express, and Developer Editions of SQL Server 2008 and SQL Server 2005 SP2 running on X86, X64, or IA64 platforms. The solution is supported on the same OSs that SQL Server supports. You can download Maintenance- Solution.sql by clicking the 100178.zip hotlink at the top of this page. Aternatively, you can download the script at http://ola.hallengren.com.

How to Back Up Databases

DatabaseBackup is the stored procedure in MaintenanceSolution. sql that performs database backups. When you call this stored procedure, it creates a backup directory, performs a database backup, verifies the backup file, then deletes any old backup file for each database you specify. You specify the database to back up and other details using an EXECUTE statement such as

EXECUTE dbo.DatabaseBackup@Databases = 'USER_DATABASES',@Directory = 'C:Backup',@BackupType = 'FULL',@Verify = 'Y',@CleanupTime = 24

This statement tells DatabaseBackup to perform a full backup of all user databases, verify the backups, then delete any backup files older than 24 hours.

Let’s take a closer look at the EXECUTE statement. You use the @Databases parameter to specify the databases you want to back up. You can select all user databases by specifying 'USER_DATABASES' or select all system databases by specifying 'SYSTEM_ DATABASES'. You can exclude databases from the user databases using the syntax

@Databases =  'USER_DATABASES, -Database1, -Database2'

where -Database1 and -Database2 are the names of the databases you want to exclude. With this setup, you can, for example, have a backup strategy for one database and another backup strategy for all other databases.

Alternatively, you can back up an individual database by specifying the database’s name, following the syntax

@Databases = 'Database1'

To back up two or more individual databases, follow the syntax

@Databases = 'Database1, Database2'

Besides the DatabaseBackup stored procedure, the IndexOptimize and DatabaseIntegrityCheck stored procedures use the @Databases parameter.

The @BackupType parameter identifies the backup type. The stored procedure can perform full backups ('FULL'), differential backups ('DIFF'), or transaction log backups ('LOG') on the databases you specify.

You use the @Directory parameter to specify the backup root directory, under which DatabaseBackup creates a directory structure consisting of the SQL Server instance name, database name, and backup type. So, for example, if your SQL Server instance is Server1$Instance1 and you specify 'AdventureWorks' for the @Databases parameter, 'C:Backup' for the @Directory parameter, and 'FULL' for the @Backup- Type parameter, the stored procedure creates the directory structure C:BackupServer1$Instance1AdventureWorks FULL. Each backup file’s name consists of the SQL Server instance name, database name, backup type, and date and time of the backup. In this example, the filename would be Server1$Instance1_ AdventureWorks_FULL_20080904_000001.bak.

The last two parameters are @Verify and @CleanupTime. The @Verify parameter controls whether the backup file should be verified ('Y') or not ('N'). The @CleanupTime parameter specifies when to delete old backup files, assuming the backup and the verification (if that was selected) was successful.

How to Optimize Indexes

Although there aren’t any set rules when it comes to determining when and how to optimize indexes, there are some basic concepts to guide you:

The IndexOptimize stored procedure incorporates these concepts into its logic. This stored procedure categorizes indexes into six groups based on their level of fragmentation (high, medium, or low) and whether LOB columns are present (LOB) or not (NonLOB). The parameters that represent these groups are as follows:

IndexOptimize uses the avg_fragmentation_in_ percent column in the sys.dm_db_index_physical_ stats dynamic management view (DMV) to obtain the percentage of fragmentation in each index. Using the threshold limits you set in the @Fragmentation- Level1 (lower threshold) and @FragmentationLevel2 (upper threshold) parameters, it places each index in the appropriate group. Indexes with fragmentation levels higher than the upper threshold go into one of the high fragmentation groups. Indexes with fragmentation levels at or between the two thresholds go into one of the medium fragmentation groups. Indexes with fragmentation levels under the lower threshold go into one of the low fragmentation groups.

IndexOptimize can also place indexes in one of the low fragmentation groups based on their page count. Indexes under the size specified in the @Page- CountLevel parameter go into one of the low fragmentation groups.

For each group, you can select one of the following actions:

So, for example, the EXECUTE statement in Listing 1 tells IndexOptimize to rebuild indexes that are more than 30 percent fragmented, online if possible (no LOBs). If these highly fragmented indexes have LOBs, an offline rebuild is to be done. Indexes with a fragmentation level between 5 percent and 30 percent are to be reorganized and have their statistics updated. Indexes that are less than 5 percent fragmented or have fewer than 1,000 pages aren’t to be touched. IndexOptimize uses T-SQL’s ALTER INDEX command to rebuild and reorganize indexes.

How to Check Databases’ Integrity

The DatabaseIntegrityCheck stored procedure uses T-SQL’s DBCC CHECKDB command to perform integrity checks. Using this stored procedure instead of the Database Maintenance Plan Wizard’s Check Database Integrity Task might mean that you don’t have to install the hotfix for bug 50001012. (The Check Database Integrity Task can lose database context under certain circumstances in SQL Server 2005 builds 3042 through 3158—see support.microsoft.com/kb/934458.)

The EXECUTE statement you use to run DatabaseIntegrityCheck is simple. You just need to specify the databases you want to check. For example, the statement

EXECUTE dbo.DatabaseIntegrityCheck@Databases = 'USER_DATABASES'

checks the integrity of all user databases.

All Types of Information Are Readily Available

The DatabaseBackup, IndexOptimize, and Database- IntegrityCheck stored procedures have thorough logging and error handling. The start and end time, command text, and output are logged for each command in the stored procedures. Additional information is logged for the IndexOptimize’s ALTER INDEX command, as Figure 1 shows. All command information is immediately written to a log file. You can find information about how the stored procedures handle errors in MaintenanceSolution.sql’s documentation at http://ola.hallengren.com/Documentation.html. The documentation also includes information about how to use each stored procedure as well as answers to frequently asked questions.