Log File Growth and DBCC DBREINDEX

[Sunite]

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.




/>