Index Reorg/Rebuild Maxes out log file

• Samwell

  Mr or Mrs. 500

  Points: 579

  More actions

  November 22, 2016 at 12:46 pm

  #337630

  I have an instance of ss2k8 r2 sp3 with a 52GB table which has a fragmented primary key index. The last run of the job that was supposed to reorg/rebuild the indexes failed because the log file became full. The database is in simple recovery mode.

  Is there any way to predict how large of a log file will be required to reorg/rebuild the index?

  Also, change data capture is enabled on this table. Will this impact the index reorg/rebuild in any way?

  Thanks for any pointers.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 24, 2016 at 7:41 pm

  #1914469

  There is no way to tell the exact number, but a rough estimation would be 3 times the size of the table, including all the indexes.

  In case of 52GB table, 200 GB of free space for LOG records should be enough.

  But if you have some fat covering non-clustered indexes you may need more than that.

  _____________
  Code for TallyGenerator