Shrink, then reindex, or ...

  • ... reindex, then shrink a database?  Does it matter?


  • In general Indexing makes your log file to grew depends on your recovery model. If you want to keep your log file small re index first and then shrink the log file and data file. But when you talk in terms of perf it doest make any difference


  • Reindexing will cause your database to have extra free space (the space freed by the reindexing). Shrinking gets rid of excess free space. So, having said that, which do you think you should do last?

    I always shrink AFTER I reindex. That way I get rid of the free space created by the reindex.


  • I agree with Bill. Shrink after reindex.


  • I don't believe in shrinking the database.  Your just setting yourself up again to grow the datafiles.   Why would you want to keep growing and shrinking a datafile?

    Something else to keep in mind while reindexing is that extra space is needed in the datafile to rebuild the indexes.   At least as much free space as your largest index (probably a clustered index) because SQL Server makes a 'copy' of the index before removing the old index.

    "Keep Your Stick On the Ice" ..Red Green

  • ok, but does shrinking cause fragmentation of indexes?

    I run the script in the BOL to defrag my indexes on a weekly basis.  If I run the script to defrag the indexes and immediately run it again it has nothing to do on the second run.

    If I shrink the database and then run the defrag script it seems like the indexes are fragmented again (although no pages are removed).  This is why I asked the question.

    I think Jeff's comments above about not shrinking the database are interesting - how many people shrink their databases and how many leave them alone?


  • I would bet that shrinking a database would move the pages around....causing fragmentation.  Just a guess.


    Why shrink if you know you are going to need the space soon?   If it got that large once then in a 'normal' environment I am sure it is going to get there again 🙂

    "Keep Your Stick On the Ice" ..Red Green

  • Here is my 2cents,

    There are two options to shrink file/Database. If you use "TRUNCATEONLY" option, since iti s not mvoing any data and just take off tailed unused space back to operating system, it shouldn't cause fragmentation. Since our testing environment doesn't have enough space, I use this often to save some space.

    So, the problem is "NOTRUNCATE" option, I'd guess and there only concern about clustered index since non-clustered index is already fraged as it is. Anyway, when you use this option, it still has to follow the clustered index/table rule which is store data based clustered index key which is implemented as a B-tree index structore and what the shrink file with notrucate option is doing similary to the disk defragment to have contiguouse sectors as much as possible and moving files when it fits the rule under clustered index/table. So, it is not doing fragment but just push back the unused page to the end of the file so when you use "TRUNCATEONLY", it will actually release the file size to operating system.

    So, by doing index defrag, it will arrange the data structore with right fill factor and by doing that, there will be fragment and shrink file after that makes more sense to me.

    So, I agree with Bill


  • Iam very new with sql

    Question: how and what is the syntax to reindex the log files?


  • Alvin,

    Log files don't have indexes.

    Check out the BOL for information on:




    BOL: Books OnLine = Microsoft SQL Server's Help

    Found at Start>Programs>Microsoft SQL Server>Books OnLine

    Installed as part of the Client Tools

Viewing 10 posts - 1 through 9 (of 9 total)

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