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.
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-
Backup/Restore simply copies the database with free space on destination where we will not be provisioned with 10 TB
Still looking into transactional replication if it will work because TR might not be ideal to move entire database as part of DB migration
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 !!