Dropping and Rebuilding Large Objects
When you drop or rebuild large indexes, or drop or truncate large tables, the SQL Server 2005 Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.
The Database Engine avoids the allocation locks that are required to drop large objects by splitting the process in two separate phases: logical and physical.
In the logical phase, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. With a clustered index that is dropped, the data rows are copied and then moved to new allocation units created to the store either a rebuilt clustered index, or a heap. (In the case of an index rebuild, the data rows are sorted also.) When there is a rollback, only this logical phase needs to be rolled back.
The physical phase occurs after the transaction commits. The allocation units marked for deallocation are physically dropped in batches. These drops are handled inside short transactions that occur in the background, and do not require lots of locks.
Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.
Deferred drop operations do not release allocated space immediately, and they introduce additional overhead costs in the Database Engine. Therefore, tables and indexes that use 128 or fewer extents are dropped, truncated, and rebuilt just like in SQL Server 2000. This means both the logical and physical phases occur before the transaction commits.