Post reply

DBCC DBREINDEX and MDF file size

  • October 21, 2004 at 12:53 pm

    #86788

    Quick question about reindexing as part of a maintenance plan.  I know that it will increase the log file size if you don't switch the database to simple or bulk_log, but can it increase the size of the data file?  I cannot find anything saying that it will, but I just thought I would ask.

     

    Thanks!

  • October 21, 2004 at 1:34 pm

    #527530

    It could change the data size, but unless it's been a really long time since you've re-indexed, or your data has changed significantly (gotten larger) you'd have to assume the difference in size would be relatively small, since you're replacing old index data with new data.

  • October 22, 2004 at 6:58 am

    #527632

    It has been my experience that DBCC DBReindex definitely requires additional disk space.  If you have not reindexed the data previously be prepared to need almost twice as much disk space as your data actually requires.

    It was my impression that this issue was supposed to have gone away with SQL 2000 in that it was to use the tempdb to do this work, but in reality that has not been the case.

    If anyone knows of a way to avoid this I would be glad to hear about it!

     

  • October 22, 2004 at 7:09 am

    #527638

    I have done the DBCC DBREINDEX on a 180+ GB database. It did not increase the database (.mdf) file. It DID increase the log (.ldf) file by quite a lot.

    The end result was that the .mdf file was SMALLER and the .ldf was LARGER. Why? Well the .mdf has the index data and the reason to run DBREINDEX is to fix fragemented indexes. A fragged index is one that is spread over more pages than it needs to be. So when it's reindexed, the index takes up less space - which equates to a smaller .mdf. However as it drops the indexes prior to rebuilding them, it needs to store the 'original state' in the log file so it can rollback if necessary (it's an all or nothing action).

    Once the dbreindex is finished, just truncate the log file (BACKUP LOG dbname WITH TRUNCATE_ONLY) to reclaim the extra space.

    -SQLBill

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply