Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 338

How it works...
The recovery process of copying data to a backup or restoring data from a backup is a
multi-phase process. A differential backup contains sufficient log records to allow rolling
forward the active transaction log records as part of restoring each backup. Each database will
maintain a transaction log to roll back uncommitted transactions to bring the database into a
transactional consistent and usable state, which is called point-in-time. The process of rolling
forward any uncommitted transactions and bringing the database online is known as recovery.
A roll forward set is defined by restoring one or more full backups such as database backup
or partial backup. In the case of differential backups, if files were added to the database as
in the differential base, restoring a differential backup will overwrite pages in a roll forward
set with data from the differential backup. The restore sequence operation corresponds to an
individual phase such a data copy, redo (roll forward), and undo (roll back).
Implementing sustainable index
maintenance practices for a VLDB & 24/7
environment
The index maintenance is one of the optimizing applications steps, and implementing
sustainable index maintenance for a VLDB environment should be evaluated from several
different perspectives. There are many cases for any performance hit will see on the
databases, next to not having indexes or indexes that are very fragmented. Also, the issue will
be multi-fold where fragmented indexes will take more space than to maintain the indexes
alone impacting on all aspects of the database activities.
In this recipe, we will go through the strategy of implementing sustainable index maintenance
practices for a very large database, and a database environment that must be available 24/7.
Getting ready
The process of implementing index maintenance practices includes the defragmentation of
indexes by identifying the indexes that require maintenance activities, such as re-indexing
or reorganizing.
In order to perform the ONLINE REBUILD method and DATA_COMPRESSION method, the SQL
Server instance must be the DataCenter edition or Enterprise edition.
The sustainable index maintenance practice includes the list of indexes that is going for index
scan and obtain a list of unused indexes on tables.
The instance must be SQL Server 2008 version or higher, and the database size must be over
250 GB in size.