Migrate Tables to another file

Question

Hello,
I have database of size 5TB. There is a File group (FG1) created and in it, there are two files (F1 and F2) used for user tables (When tables are created they use the files to load the data).
Now, I want to create F3 and migrate all the data from F2 to F3. My question is what is the best way to do it?

So far I tried Shrinkfile with EmptyFile argument and that took way longer as the data to be moved is huge.

Please suggest a better, efficient way to do this.

Thanks

Answer 1

To my knowing there is no other alternative. If you are going to move terabytes of data, you will need some patience.

Why do you want to move data from F2 to F3 in the first place?

Answer 2

Hi，@Kman

Welcome to Microsoft T-SQL Q&A Forum!

Have you tried to use auxiliary file groups? The general process is as follows:

1. Create auxiliary file groups.
2. Add the data files to the auxiliary file group.
3. Move the table to the secondary filegroup by moving the clustered index with the primary key constraint.
4. Move the table to the secondary filegroup by moving the clustered index without the primary key.

But you need to pay attention to a problem . If there are empty files in the file group , if you directly migrate the data to other files , there will be some files that cannot be emptied . Although the MS document mentioned that it has no effect , I believe it's only a matter of time before encountering this kind of problem.
Please refer to this document.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.