All we need is an easy explanation of the problem, so here it is.
Have a single 500gb DB with a single file group consisting of 4 data files. This was due to old SAN volume space restrictions that are no longer in play on the new SAN. Would like to consolidate these 4 data files back down to a single data file if possible. What are the different ways to consolidate these data files down to a single data file?
SQL 2019 CU14
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
As mentioned in the comments, there are a number of reasons why you would want to maintain multiple files, primarily from a performance perspective.
If you’re adamant you want to combine everything into a single file, you can use DBCC SHRINKFILE with the EMPTYFILE option. This option moves all data from the specified file and distributes it across the remaining files in the filegroup. You repeat this three times, once for each of the files you wish to remove, leaving behind a single file with all of the data.
USE [database_name] GO DBCC SHRINKFILE ('file2', EMPTYFILE) GO ALTER DATABASE [database_name] REMOVE FILE [file2] GO
As each file is emptied it redistributes the data across the remaining files, eventually pushing all data into the last remaining file.
An alternative option is to create a new filegroup with a single file and rebuild all objects in the current filegroup into the new filegroup.
The disadvantage of this option is that you need double the amount of disk space until the whole data move is finished. The EMPTYFILE method allows you to recover space immediately as each file is emptied, reducing the space requirements.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂