The question:
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
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0