question

KIRUBELKEBEDE-0822 avatar image
0 Votes"
KIRUBELKEBEDE-0822 asked ErlandSommarskog commented

Migrate Tables to another file

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

sql-server-transact-sqlsql-server-migration
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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?

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We compressed the table and rebuild the indexes with page compression as well. Huge storage was saved but we still need to shrink the files to reclaim the space and give it back to the OS. 80% is a unused at this point.

0 Votes 0 ·

But that does not explain why you would move the data to a new file. You could just as well shrink the current file.

0 Votes 0 ·

The shrink file takes very long and we don't want to do that on the Prod environment. I am just looking for a better and quicker process that can release the storage back to the OS.

0 Votes 0 ·
Show more comments
BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@KIRUBELKEBEDE-0822

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.