2

I have a 170 GB table in a database. It has 70% fragmentation on its index (index has multiple columns in it). The last time I tried DBCC REINDEX the transaction log blew up in the hundreds of GBs.

  1. Should I drop the index and recreate it or do a DBCC REINDEX?
  2. What is the best way to do either one?
  3. How can I do either one while keeping the transaction log as small as possible?
1
  • 1
    By the way, DBCC DBREINDEX is deprecated. It's probably not the cause of your log growth issues, but should you still think about moving to ALTER INDEX - it is much more flexible as well.
    – Paul White
    Commented Jan 19, 2015 at 7:23

2 Answers 2

2

DBCC REINDEX is minimally logged in the SIMPLE or BULK_LOGGED recovery model. If the database is currently in the FULL recovery model, consider toggling it to BULK_LOGGED for the index maintenance.

See http://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx

3
  • 1
    Would that destroy the backup chain? Commented Jan 17, 2015 at 23:10
  • 1
    @Justtin_Dearing, switching between these 2 recovery models does not break the log backup chain. However, you can't perform point-in-time recovery until you switch back to FULL and perform a log backup. See technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx.
    – Dan Guzman
    Commented Jan 17, 2015 at 23:20
  • When you perform bulk logged operation in BL recovery model you just loose PIT for the period you performed the operation. For other transaction log backups taken you can do PIT
    – Shanky
    Commented Jan 18, 2015 at 11:50
2

Starting from 2008 onwards Index rebuild is fully logged in full recovery model. If you rebuild huge index in full recovery model its bound to produce too much logs. So in this table you have how many indexes? Are you rebuilding all such indexes in one go. If you are doing this you must consider doing it piece meal. One index at a time

Of course you can switch to bulk logged recovery model when rebuilding index but you would loose point in time recovery for period the index rebuild operation is going. After index rebuild is done and if you still continue with bulk logged recovery model and some transactions are done which are not defined as minimally logged in BL recovery model they would be fully logged

Consider below before switching to Bulk Logged Recovery Model

  1. Take transaction log backup.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.

  2. After performing the bulk operations, you immediately switch back to full recovery mode.

  3. After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.

  4. If you are switching to bulk logged try to make sure index rebuild is only operation going on, although this is not a restriction.

  5. Also Logging in any recovery model does not affects the physical file size. So dont expect in bulk logged recovery model size of log file would decrease

Use Ola Hallengren for index rebuild and stats update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.