Does rebuilding of indexes cause the transactional log file to grow?

Question

I had about 50% of the databases where indexes were fragmented more 50%, so instead of rebuilding only the fragmented indexes I have rebuild all indexes in task scheduler. After that I noticed that log file grew about 3 gig. Is there a possibility why logfile will grow after rebuilding indexes? Is there any process that I have to run now to prevent further growth? I have compare the logfile of some databases and where log file grew before and after reindexing.

Answer 1

Hi @Avyayah ,

The index rebuild operation runs by default in a single long-running transaction, that prevents the Transaction Log space reuse while rebuilding a large index.
Switching to the BULK_LOGGED recovery mode while doing an index rebuild operation will reduce the amount of transaction log generated. If you're considering using the BULK_LOGGED recovery mode, beware that you lose the ability to do point-in-time recovery to ANY point covered by a transaction log backup that contains even a single minimally-logged operation. The operations you should perform if you're going to do this are:

• In FULL recovery mode, take log backup immediately before switching to BULK_LOGGED.
• Switch to BULK_LOGGED and do the index rebuild.
• Switch back to FULL and immediately take a log backup.

Please refer to this article which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

Yes, rebuilding indexes will take a toll on your transaction log file. Particularly if you are in full recovery. Basically, an index rebuild copies the index to another place. And if it is a clustered index, the entire table is copied.

There is all reason to only rebuild index that are fragmented, and a good maintenance solution can help you with that. See http://ola.hallengren.com for the solution that most DBAs use. Ola script will only rebuild the indexes where the fragmentation is high enough.

On the flip side: next time you go on this rebuild craze, the log will not grow that much provided: 1) you did back up the transaction log at some point. 2) You did not shrink the log file.