SQL Server Performance

How to reclaim the disk space used by REINDEX

Discussion in 'General DBA Questions' started by ykchakri, Jun 24, 2004.

  1. ykchakri New Member

    As everybody here knows, a DBCC DBREINDEX on a database creates lot of empty space in the database. Now I'm trying to reclaim this disk space by shrinking the database using DBCC SHRINKDATBASE.

    But SHRINKDATBASE is increasing the logical fragmentation of all the indexes into 90s (Probably due to the page movement caused by shrink). And a SHRINKDATBASE with 'truncateonly' option is not reducing the size at all.

    So, how can I shrink a database (after DBCC DBREINDEX) without fragmenting the indexes ?
  2. Luis Martin Moderator

    Without fragmenting the indexes, I think no way.
    That is way I only rebuild critical tables, but may be you are doing the same.

    Luis Martin

    All postings are provided “AS IS” with no warranties for accuracy.

  3. ykchakri New Member

    Thanks Luis,

    The only reason we do DBREINDEX is to de-fragment indexes and there is no point if a shrink after DBREINDEX fragments the indexes again.

    But, I can't understand why the 'truncateonly' option never works. This is supposed to truncate the free space from the end of the file. As I can see there is lot of free space in the data file, but this option does not release even a single byte out of it.
  4. satya Moderator

    If there is only SPECIFIC NEED to shrink the database then do it, otherwise DBREINDEX and SHRINK DATABASE works opposite way and will have no use rather using server resources.

    If you don't have disk space problems on SQL server then leave'em.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. ykchakri New Member

    Hi Satya,

    I have multiple databases on the same disk that I want to reindex and each database is growing almost double in size after a reindex. And I don't have enough disk space to accommodate this growth. So, I want to reindex few databases at a time and shrink the extra disk space used by these before reindexing another set.

    If the DBCC SHRINKDATABASE with truncateonly option do what it's supposed to do, then I'm all set. But for some reason this does not shrink the database at all. Do you know how to make this command work ?
  6. satya Moderator

    In that case can you define DBREINDEX specific indexes instead of complete database-indexes, as I'd followed similar approach for one of our service.

    And the truncateonly option should work and check if there any uncommitted transactions are pending in the log avoiding the completion of this statement.


    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. derrickleggett New Member

    You need to use DBCC SHRINKFILE instead of shrinkdatabase and shrink the log file only. This will not affect your indexes if you only shrink the log files. You might consider:

    1. Stop your transaction log backups and switch the recovery mode to simple.
    2. Run the DBCC DBREINDEX.
    3. Switch the recovery mode back to full and resume your transaction log backups.


    When life gives you a lemon, fire the DBA.
  8. ykchakri New Member

    Thanks for your suggestions. Since I'm keeping the database in Bulk-Logged mode before DBREINDEX, the transaction log file in not growing much (though the subsequent transaction log backup creates a huge backup file).

    My main concern is with the data file growth, not log files. Any more suggestions to reduce this growth without increasing index fragmentation.
  9. ykchakri New Member

    And here is some information from the dbcc shrinkfile command:
    DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
    ------ ------ ----------- ----------- ----------- --------------
    19 4 1409832 128000 651120 651112

    What I understand from this is, File 4 is currently occupying 1409832 pages, though only 651120 pages are being used by data in this file. But, shrinkfile still can't shrink this file. Any ideas ?
  10. satya Moderator

  11. ykchakri New Member

    On a filegroup named 'INDEX' which is seperate from the filegroup (PRIMARY) for data.
  12. satya Moderator

    Have you tried reindexing only required indexes and check the space rather than all in the database?

    To be sure that you're benefiting from your defragmentation efforts, measure the performance of representative queries before and after defragmentation. You might discover that you can reduce the frequency of your defragmentation or even eliminate this data-maintenance task.

    You can also stop and restart DBCC INDEXDEFRAG without losing any defragmentation work that you've already completed.

    For more information about defragmentation, refer to the white paper SQL Server 2000 Index Defragmentation Best Practices athttp://www.microsoft.com/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page