SQL Server Performance

Reindex increase the data file size

Discussion in 'ALL SQL SERVER QUESTIONS' started by AJITH123, Apr 9, 2013.

  1. AJITH123 Member

    Can someone suggest how to reduce the data file unused space which was increased after the reindex processes. Do not want to shrink the file since it is a time consuming task and may cause the index fragmentation. Any other methods?
    [SQL server 2005 version]
  2. FrankKalis Moderator

    Why do you want to reduce the size of the file at all? I guess next time you reindex it'll grow again and that this will lead to a yo-yo effect and increase file fragmentation even more.
  3. AJITH123 Member

    Understood. The table is quite huge, almost 5 Billion records, and the index created unusual empty space, it grabbed almost 600 gb extra space from different drives, so it worried me :(

    The confusion is, when i check some other db comparatively quite big than this, see the below sizes, why this got more empty space after index..?
    database_size|unallocated space |index_size
    -----------------------------------------------------------------------
    5116298.43 MB|223686.31 MB |2369913424 KB
    -----------------------------------------------------------------------
    2466255.00 MB| 995059.77 MB |717894840 KB
    Thanks
  4. FrankKalis Moderator

    Okay, understood. But still the next reindex will reclaim the space and the file will grow again. You may want to read carefully through this one. Especially through the last chapter that mentions disabling an index before rebuilding it to save space.
  5. AJITH123 Member

    Thanks Franks. Let me have try..
  6. Charandeep Nayyar New Member

    fillfactor of 90 (= 10% free space) applied to that table; thus, the REBUILD will restore that fillfactor
  7. Luis Martin Moderator

    Rebuild don't mean shrink at all.
  8. Charandeep Nayyar New Member

    Shrinking after reindexing is pointless!

    You will just re-fragment the index! Shrinking removes the slack space by reallocating data within the DB
  9. Luis Martin Moderator

    I only try to back to the original question. That's all.

Share This Page