Index Defrag and Transaction Log

  • I was running some index defragments on one of our databases this weekend using DBCC INDEXDEFRAG (I know this is being removed in future versions) and it completely ate up the space on my transaction log's drive which caused several unpleasant things to happen.

    So i had a couple questions about that, first why it eating up so much transaction log space? And would i see less transaction log space use if i used ALTER INDEX?

  • Reorganise (ALTER INDEX ... REORGANISE or DBCC INDEXDEFRAG) is fully logged always. The more fragmented the index, the more it has to do and the more log space it generates.

    Rebuild (ALTER INDEX... REBUILD, DBCC DBREINDEX) is fully logged in full recovery, minimally logged in bulk-logged and simple recovery (for 2005, there were changes in 2008). It always completely recreates the index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Okay thanks for the information, I guess I'll have to change our backup plan a little to accomodate it.

  • Make sure that the tran log has space to grow. If it's running out of space during routine maintenance, it needs to be larger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hmmm.. it filled up the entire 135 GB drive we have allocated for the log files in less than an hour, we backup the transaction logs every hour, during normal operations the logs are usually only a couple GB. I was running 8 batchs of index defrags concurrently, which was a first since we're still finalizing our routine maintenance, and i didn't realize they would fill up the logs that fast.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply