Management of File data

MSSQL-Feature-Image

File data is used to store all object and data of a database.
When File data have object and data, it will not be able to move to other filegroup.

How to move data file to other filegroup:
No, you cannot move data file to other filegroup.
However, you can empty current data file, delete this data file and then create a same name data file at same path with different filegroup.
The following is the T-SQL to move data file named “TEST_FD01.ndf” to filegroup “FG01” at database named “Hello”:

How to combine multi data file within same filegroup:
Use DBCC SHRINKFILE to empty data file and then delete the data file.
The following show how to combine test_1, test_2 to test_4:

How to shrink data file’s size:
After we delete table within data file, the size of data file will not auto-shrink.
The following is to shrink the size of data file named “test_1”:

Author: Joe Chan