Optimising file shrink and index reorganization operations

Question

I'm archiving my SQL tables. So I am shrinking my files (because I need the free space) and re-organising my indexes(to defragment them). I know shrinking a file causes the indexes to be fragmented. So after shrinking my file, I check the fragmentation % of my indexes and if the degree of fragmentation is greater than 10%, I reorganise them. I am reorganising it partition by partition by employing the following code:

ALTER INDEX <index_name> ON <table_name> REORGANIZE PARTITION = <partition_number>

And for shrinking files, the following code is being employed:

DBCC SHRINKFILE ('<file_name>',<desired_size>)

Now so far, I have been shrinking my files, reorganising my indexes only to find that the file pertinent to the index has again bloated up, requiring to be shrunk up again; the said shrinking again causes fragmentation(as expected) requiring defragmentation again. I have had to perform these operations several times to get rid of index fragmentation and set the file to a desired size.

Is there a way to optimise the vicious cycle of shrinking -> defragmenting -> shrinking -> defragmenting? Is there a way to reduce the number of steps?

Answer 1

Move your indexes into a new pre-sized (but fairly small) filegroup. Moving them will involve a rebuild, and your file will be small. If you do this to all the objects in your current filegroup you will be able to remove it completely, keeping only the new, tight, filegroup, with only unfragmented indexes.

And please stop shrinking your database files. If you need the space on a temporary basis (eg, for loading data prior to transformation) and need to reclaim it after for other uses, then consider using a different database for that temporary stuff, so that you can drop that database once your loading (or whatever) is done.