Logging Behavior

Default incremental reorganization logging includes the extent allocation, extent version update, index row deletion, index row insertion, extent deallocation, and scan position update, in that order.

This logging sequence ensures complete recoverability of the reorganization. However, this sequence may not enforce the dump tran sequence.

Turn on either full logging for reorg rebuild or the general full logging for all options in the existing database to ensure that the incremental reorganization enforces the dump tran sequence, an extra log of the page image is required for every destination data page created by the reorganization.

The default logging behavior consumes the following log space by reorg defrag:

• Number of extent allocation log records –
  • Max value: (32 / ‘number of preallocated   extents’).
  • Min value: 1.
• Number of extent version update log records – Number of the destination extents allocated in this transaction (Maximum 32).
• Total number of index delete/insert log records – 2 x (number of indexes on the table) x (number of data rows de-fragmented in this transaction).
• Number of extent deallocation log records –1 (for all the extents deallocated in this transaction).
• Number of page deallocation log records – Number of unused destination pages in the transaction.
• Number of scan position update log records – 1 (for the last scan position of this transaction).
• Extra logging to achieve full logging behavior:
  • Number of page image log records – Number of used destination pages in the transaction (Maximum 255).