Alternatives to reclaim free space after Shrink activity

Question

For couple of our SQL server databases on version SQL2017 APP team did purge to change retention on these partitioned tables which has released quite good amount of space.

Some background-

Before purge- DB size around 10 TB, free space 1.5 TB

After purge - DB size till shows 10 TB free space 7 TB

We are planning to do migration on SQL2019 and wont need initial storage because as mentioned above DB actual size has reduced well under 5 TB. However the problem is due to DB design we are unable to get much success from Purge because its been a week and we have reclaimed hardly 200 GB from that free space by shrinking. We will run into issues like blocking or never completing shrink operations which just seems to be too slow. We have used some guidance from https://eitanblumin.com/2020/04/07/troubleshooting-long-running-shrink-operations/ but still no luck

We are exploring few options below-

1. Backup/Restore simply copies the database with free space on destination where we will not be provisioned with 10 TB

2. Still looking into transactional replication if it will work because TR might not be ideal to move entire database as part of DB migration

3. I am not sure on this one but may be anyone of you can guide- DB has been created with multiple files , approx 10-12 ndf'sunder primary filegroup. Is it possible to create new files and simply make all transactions start going in new files and then later simply empty the old ndf files where shrinking is slow.

Please provide thoughts or how can above be achieved with some inputs.

Update based on comments

Thanks, We have tested in lower env with option of DBCC SHRINKFILE, EMPTYFILE. That one seems to move data from existing file in the FG to other files in same FG. It is for sure very slow, around 20 hrs for 500 GB file but seems to be marking file INACTIVE. Does that mean i will get no more data in that file and safe to DELETE the file so as to reclaim the free space

?

Thanks in advance !!

Answer 1

This is challenging but achievable with caution. One thing you can (with caution) is to look at the initial size of the data and log files and identify how much is being used and how much is free and then update the initial value. NOTE: This is not a good practice.

Backup/Restore and Transactional Replication: Limited Success The Backup/Restore approach, despite its straightforward nature, replicates the existing free space issues in the new environment, which unfortunately won't provision 10 TB of space. Similarly, transactional replication stands as a potentially inadequate solution for the complete database migration, given its complex dynamics and potential limitations.

Leveraging Multi-File Approach: A New Perspective? An avenue worth exploring could be leveraging the multi-file structure of our database, which consists of around 10-12 NDFs under the primary file group. The strategy involves creating new files to house all new transactions, a move expected to aid the gradual transition from the old NDF files and facilitate the shrinking process by avoiding slow-to-shrink older files.

If all the options don't work In an emergency situation, more space can be added to the disks where the files reside.