sickpup (2/17/2011)
Great info - thanks all. I knew shrinkfile would cause fragmentation... just didn't know how bad it would be.So, here's the gameplan:
Identify the NC indexes on the affected DB Spaces and script their creation.
Drop the identified NC indexes.
Shrink the DB file to the correct size (including space for future growth).
Rebuild the associated clustered indexes (with correct fillfactor).
Recreate the associated NC indexes (with correct fillfactor).
Buy everyone a round. 🙂
I guess my last question is - I'm targeting the indexes on the offending DB file, but should I drop/recreate/rebuild all the indexes in the DB? The errant Reindex process was targeting them all but, oddly enough, this one particular DB file is the only one that had noticeable growth.
The scheduled reindex job has been corrected, so it'll correct the fillfactor the next time it runs. And, since the other DB files didn't really explode in size, there's no need to worry about space limits or shrinkfile commands...
Just wondering if I'm cool with limiting my vengence to the single DB file and leaving the others alone.
Thanks.
Dropping all indexes will cause the table to revert to a heap. That process will cause the clustered index to be preserved until the heap is rebuilt. That's means that empty space the size of the former clustered index will be present. When you rebuild the clustered index, the heap will be maintained until the clustered index is ready. Same problem... lots of free space.
If you want to reduce the space used, then you might want to try ...
1. Drop all the non-clustered indexes.
2. Create a new file group with some decent initial size and growth settings. Make sure the new filegroup is in the BULK Logged Recovery Model so that you don't blow out the log file during the index creates.
3. Rebuild the clustered index on the existing table but pointing it with "ON" to the new filegroup. Virtually no empty space beyond your growth settings will be created on the new file group.
4. Create your non-clustered indexes on the new file group.
5. Cleanup the old file group and drop it... hopefully, it's not the PRIMARY file group.
6. Change the new file group to FULL Recovery if you need it to be.
I use the process above when I do the final reindexing on partitioned data just before I set the filegroup for that partition to Read-Only so that I'm not locking up a ton of empty space.
--Jeff Moden
Change is inevitable... Change for the better is not.