Index operation log Problems

Some complete log records and large-scale index operations can generate large data loads. Whether performed offline or online, these data loads can quickly fill up transaction logs. This may affect performance. By setting the database recovery mode to large-capacity log mode or simple mode during the duration of index operations, you can minimize the log records of these index operations. The minimum log record is more effective than the full log record, and can reduce the possibility of index operations filling up the log space.

Index operation log records

The following table lists the types of log records available for index operations and each of these operations in database recovery mode. Both online and offline index operations support these recovery modes.

Index operations	Complete	Large-capacity logs	Simple
Alter index reorganize	Full record	Full record	Full record
Alter index rebuild	Full record	Least recorded	Least recorded
Create Index	Full record	Least recorded	Least recorded
DBCC indexdefrag	Full record	Full record	Full record
DBCC dbreindex	Full record	Least recorded	Least recorded
Drop Index	The release of the index page is a complete log record, and the re-generation (if applicable) of the new heap is a complete log record.	The release of the index page will be fully recorded; the regeneration of the new heap will be recorded in the minimal way (if applicable ).	The release of the index page will be fully recorded; the regeneration of the new heap will be recorded in the minimal way (if applicable ).

For more information, see .