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:\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