Reclaim free space in partition with as little fragmentation as possible

Question

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.

Answer 1

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