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.
Problem:
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.
Best Answer
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.