Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
