Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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