0

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

1
  • 1
    You don't want to do that, for a multitude of reasons. Leave it as is. Apr 7, 2022 at 22:03

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.