Log File Growth and DBCC
DBREINDEX
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.
/>