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
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)