0

We have a db table which contained a large amount of documents. These documents have now been stripped out and as this table will never grow to this size again we are looking to claim back this space.

From reading up on this the best solution appears to be:

Create new filegroup (called secondary-we currently only have primary)

Move all user tables into this new filegroup by dropping and recreating the clustered index

Is this the best solution? I assume I would then need to shrink the primary filegroup? Is so, what's the best way to do this?

Many thanks Paul

0

All you would need to de-fragment the table by rebuilding the clustered index.

ALTER INDEX [IndexName] ON [YourTable] REBUILD

The clustered index on any table determines how it will be written to disk. When a bunch of deletes or re-writes occurs, the table becomes fragmented and wastes a lot of space. Doing this will restructure the data on the disk and should free up a lot of that wasted space.

I would say you could take it further by doing a database shrink however if the database is likely to re-grow to that size or larger, it would be a mistake to do that.

| improve this answer | |
  • The database\table will never grow to that size again-should I also shrink the database? – Paul Aug 23 '16 at 22:15
  • The answer in the attached suggests creating new file groups? – Paul Aug 23 '16 at 22:15
  • In this situation, I would tend to agree with the attached explanation as long as you create a separate filegroup to use for that table first, move that table to the file group and then shrink only that file. What we don't want to happen is to shrink the entire file for all of the other tables that may need the additional growth space. You may not need to rebuild index using that approach since it will be rewritten when its moved to a different file. – Shooter McGavin Aug 23 '16 at 22:46
  • If you want, after moving the table to a new file group, do a quick query to check for fragmentation. Here's a great article on the subject. blog.sqlauthority.com/2010/01/12/… – Shooter McGavin Aug 23 '16 at 22:52

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.