1

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.

1
  • @dan Guzman I am but we are talking about a thousand files in total so I'm looking for something a little more automated or time saving. Shrinking could end up taking weeks, providing we don't want to cause any load during production hours.
    – Foxyproxy
    Aug 13, 2022 at 16:02

1 Answer 1

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);
1
  • I will look into this after the weekend and get back to you on whether this could work for me. But either way thanks for taking the time to reply!
    – Foxyproxy
    Aug 13, 2022 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.