Home Forums SQL Server 2008 SQL Server 2008 Administration User DB Transaction Log fills up When Running Online Index Rebuild With SORT_IN_TEMPDB = ON RE: User DB Transaction Log fills up When Running Online Index Rebuild With SORT_IN_TEMPDB = ON

  • As far as I'm aware, regardless of where the data is sorted, the transaction log of the user database must fully log the operation to enable Rollback.

    In an Online Index Rebuild, this would involve:

    1) Logging the creation and all updates to the Temporary Mapping Index (this is the temporary index that records all changes made to the index being rebuilt during the operation to enable the new index to be updated again at the end of the operation) - the size of this is dependent on how many INSERT/UPDATE/DELETE's are performed against the base table during the rebuild

    2) The creation of the new Target Index

    3) The de-allocation of the Source Index

    The logging you're potentially avoiding by sorting in Tempdb is just the temporary write to enable a sort to be performed before it can be written back to the Target index (incidentally, MS has a note that says this option is ignored if it can perform the sort in memory).

    I don't believe there's been any fundamental change between 2005 and 2008 though, so I'm not so sure this behaviour should have substantially changed. The only thing I can think is that they may now have made the sort operation more minimally logged as it's not required for recovery so you may see less TLog growth in Tempdb...

    Have you got an identical copy of the database running on 2005 that you can test side by side?