SQL Server Magazine

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://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html.

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:\Backup\Server1$Instance1\AdventureWorks 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:

• Whether an index should be rebuilt, reorganized, or left untouched depends on the index’s fragmentation level. Highly fragmented indexes (typically more than 30 percent fragmented) should be rebuilt. Indexes with little fragmentation (typically less than 5 percent fragmented) should be left alone. Moderately fragmented indexes (typically between 5 percent and 30 percent fragmented) should be reorganized. (For more information about this concept, see “Reorganizing and Rebuilding Indexes” in SQL Server 2008 Books Online—BOL—at msdn.microsoft.com/en-us/library/ms189858.aspx.)
• Index fragmentation in a very small table has no impact on performance.
• When an index is rebuilt, the statistics are always rebuilt and therefore updated. However, when an index is reorganized, the statistics aren’t updated. Therefore, you might want to update the statistics after an index is reorganized.
• An index can be rebuilt online or offline. An offline rebuild is faster than an online rebuild. (Note that only the Enterprise and Developer Editions of SQL Server 2008 and SQL Server 2005 support online rebuilds.)
• When an index contains a large object (LOB) column, an online rebuild can’t be done.

Continue to page 2

   Prev. page   [1] 2     next page