3

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.

1 Answer 1

2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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