Reclaiming allocated free space from partitioned tables

Question

We have a data warehouse with about 4TB worth of datafiles. A couple of these databases consist of partitioned tables.

Since we're running out of disk space (on premise environment), we are looking to remove old data and archive some of it too. But there is a lot of allocated free space in the data files.

I'm looking for a way to get rid of this free space so we can migrate to a newer version (currently 2008 R2).

What I've read is that making a compressed FULL backup and restoring this on the new server will also remove this allocated space. Unfortunately I have not been able to find information to verify this method yet. Does anybody know if this holds true or perhaps any of you know a better way?

The issue is that a new partition is created each month. All the older partitions are just historic data. So logically SQL Server will never utilize free space in these older partitions.

We are talking about a thousand files in total so I'm looking for something automated or time saving. Shrinking could end up taking weeks, providing we don't want to cause any load during production hours.

Answer 1

What I've read is that making a compressed FULL backup and restoring this on the new server will also remove this allocated space.

A restored database is exactly like the original in terms of files and space. It doesn't matter whether the backup was compressed or not.

We are talking about a thousand files in total so I'm looking for something automated or time saving.

I suggest DBCC SHRINKFILE('file_name_here', TRUNCATEONLY); as a first step. This will release unused space from the end of the file without moving data so it should run fairly quickly. The below script will generate these commands for the specified partition scheme, assuming a temporal partitioning type partitioning column:

SELECT DISTINCT N'DBCC SHRINKFILE(N' + QUOTENAME(df.name, '''') + N',TRUNCATEONLY);'
FROM sys.partition_schemes AS ps
JOIN sys.partition_range_values AS prv ON prv.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
JOIN sys.database_files AS df ON df.data_space_id = fg.data_space_id
WHERE 
    ps.name = N'PS_Date' --specify partition scheme here
    AND prv.value < CAST('20220801' AS date);