Take the 2-minute tour ×
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.

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.

share|improve this question
add comment

1 Answer 1

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 at 12:07
add comment

Your Answer

 
discard

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.