[SOLVED] Reclaim free space in partition with as little fragmentation as possible

Reclaim free space in partition with as little fragmentation as possible

Question: In Sql server 2012 what is the best way to reclaim as much reserved space as possible while having as little fragmentation as possible?

Background:

Our SQL server is running low on disk space and as a part of HW+SW upgrade we are going to move data files to different server - for this reason we want to reduce size of datafiles (to prevent unnecessary moving of 'reserved space'. We are speaking about tearbytes). I also want to perform this partition by partition to be able to run overnights and limit production impact.

One approach I tried (per partition on the heavy consumer table with single index):

ALTER TABLE <Tbl>
REBUILD PARTITION = <PartitionId> WITH (DATA_COMPRESSION =  PAGE) 
GO

--I know this is bad practice - but I need to reclaim space to speed up moving
DBCC SHRINKFILE(<PartitionName>, target_size = 10 )
GO

-- This is to mitigate the impact of shrinkfile
ALTER TABLE <Tbl>
REBUILD PARTITION = <PartitionId>
GO


--Run this in the end (and I run also between the individual tasks) to see impact on index fragmentation
SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(<DbName>), OBJECT_ID(<TblName>), <IndexId>, <PartitionId>, 'SAMPLED');  
GO

In test environemnt this yield great results for some partitions (0% gragmentation and near 0% 'wasted' space on reserved space. Wasted by considering that next stage is moving data over wire), but I have a case of partition that SHRINKFILE reduces the size significantly, but causes 99.99% fragmentation; the REBUILD solves the fragmentation, but doubles the filegroup size (half being reserved space) - which is probably expected as rebuild creates index from scratch. If I shrink afterwards I can reclaim the space, but again get the large fragmentation. And this can go in circles

I'm now trying to run reorganize on shrinked filegroup:

ALTER INDEX <IdxName> on <Tbl> REORGANIZE PARTITION = <PartitionId>

As this should hopefully fix the index fragmentation without growing the datafile. However:

is that a good idea to run Reorganize on 99.99% fragmented index?
will the result be comparable/inferior/superior to running rebuild?

Another option I'm considering is to rebuild the partition to brand new filegroup, but this would require manipulating partition schema - and I want to keep the process as simple as possible.

Solution

What about backing up the database using compression and restoring it to the new server. Backups do not include unused space.