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?