Q
I have a 30 gigabyte (GB) database, and I use the Full recovery model. Whenever I use the database consistency checker (DBCC) statement DBCC DBREINDEX to reindex certain large tables periodically, I change the recovery model to Bulk_Logged, then return it to Full after the reindexing is complete. I expected that this change would help me avoid huge transaction log file growth, but the subsequent log file backup is always enormous—around 15 GB. Logically, the data in the database is the same as before the reindexing, except that the indexes are reorganized, so why is the log file so large? And how can I avoid such significant growth?

A
Yes, the data is the same, but the indexes are on completely new pages. When you perform a DBCC DBREINDEX, SQL Server logs only extent allocation (eight-page units) instead of each row or page that has been changed. This type of logging avoids physical file corruption in the event of system failure and it minimizes the impact that more detailed logging would have on throughput.

When you back up the log, SQL Server has to back up the pages allocated in those extents so that they can retain database backup and log backup consistency. If SQL Server didn't back up those pages, you wouldn't be able to switch back to the Full recovery model until you did a complete database backup. You have to be able to restore the database from the last full backup and any differential backups, as well as any later transaction log backups.