Choosing a Recovery Model for Index Operations

Some fully logged, large-scale index operations can generate large data loads that can cause the transaction log to fill quickly whether the operation is executed offline or online. This can affect performance. You can have these index operations be minimally logged by setting the recovery model of the database to bulk-logged or simple for the duration of the index operation. Minimal logging is more efficient than full logging and reduces the chance of the index operation filling the log space.

Index Operation Logging

The following table lists index operations and the type of logging available in each database recovery model for those operations. These recovery models are supported for both online and offline index operations.

Index operation Full Bulk-logged Simple

ALTER INDEX REORGANIZE

Fully logged

Fully logged

Fully logged

ALTER INDEX REBUILD

Fully logged

Minimally logged

Minimally logged

CREATE INDEX

Fully logged

Minimally logged

Minimally logged

DBCC INDEXDEFRAG

Fully logged

Fully logged

Fully logged

DBCC DBREINDEX

Fully logged

Minimally logged

Minimally logged

DROP INDEX

Index page deallocation is fully logged; new heap rebuild, if applicable, is fully logged.

Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged.

Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged.

For more information, see Choosing the Recovery Model for a Database.

See Also

Concepts

Minimally Logged Operations

Other Resources

CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
ALTER DATABASE (Transact-SQL)
DROP INDEX (Transact-SQL)
DBCC INDEXDEFRAG (Transact-SQL)
DBCC DBREINDEX (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance