I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup.


A 315GB compressed Clustered Index (page compression) resulted in a filegroup that expanded to over 650GB. (Uncompressed, this table is north of 2TB.) The free space is not limited to the end of the file, so it cannot be truncated - to shrink this file will be slow and likely result in increased fragmentation.

Let me know if you know how I can create this index without the excessive growth.


I believe I've found my own answer to this question. When setting SORT_IN_TEMPDB on, the filegroup no longer expands unpredictably, and a shrink with truncate only is successful in recovering space which is all trailing space. If trying this, note that this could result in additional space requirements for TempDB.

  • SORT_IN_TEMPDB causes the temporary mapping index of your presumed online operation to be created in tempdb. – usr Jun 26 '14 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.