move files to different file group

Question

Post reply

move files to different file group

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 3, 2010 at 3:32 am

#236136

HI,
I have a database containing only one table .The database have 6 files including mdf & ldf..Now all the files are in primary file group..I like to move 3 secondary files to new file group. How can i achive the same??

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Shivrudra SSCrazy Points: 2835 More actions · Answer 1

Why you want to move these files to new filegroup..

What is the size of database.

Is these 6 files located on different drives.

Regards,
Shivrudra W

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I don't believe you can move files from one filegroup to another.

With all the files in one filegroup, that table is spread across all of them. Tables may only be on one filegroup (other than when partitioned), so if the files could be moved, the table would be spread across multiple filegroups, which is not allowed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sivaprasad SSCertifiable Points: 6183 More actions · Answer 3

1. To move the file, Empty the file ,using DBCC SHRINKFILE with EMPTY option

EMPTYFILE

Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

2. Detach the files to be moved to different filegroup

3. Create the new filegroups using ALTER DATABASE

4. Attach the files to the Filegroups created.

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sivaprasad (8/3/2010)
2. Detach the files to be moved to different filegroup
...
4. Attach the files to the Filegroups created.

How? Detach detaches an entire database, not a file. Same with attach.

Did you try this process or are you guessing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

I think what you've have to do is add a new filegroup and new files, then rebuild clustered indexes for some tables on that new filegroup to move the data.

Once you have enough free space, I believe you can use the EMPTYFILE option to clear out a file and then do a drop to remove the file from that primary filegroup.

sivaprasad SSCertifiable Points: 6183 More actions · Answer 6

sivaprasad (8/3/2010)
1. To move the file, Empty the file ,using DBCC SHRINKFILE with EMPTY option
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
2. Detach the files to be moved to different filegroup
3. Create the new filegroups using ALTER DATABASE
4. Attach the files to the Filegroups created.

By using DBCC SHRINKFILE with EMPTY option and ALTER DATABASE REMOVE FILE remove the files which has to be moved.

Create new Filegroup and Create the database files on the new filegroup.

It is a workaround as Files cannot moved from a filegroup to another directly.

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

sivaprasad SSCertifiable Points: 6183 More actions · Answer 7

GilaMonster (8/3/2010)
sivaprasad (8/3/2010)
2. Detach the files to be moved to different filegroup
...
4. Attach the files to the Filegroups created.
How? Detach detaches an entire database, not a file. Same with attach.
Did you try this process or are you guessing?

Thanks for pointing it out.

Detach will detach all the files on a Database.

Hence the option is remove the file from the current filegroup using ALTER DATBASE REMOVE FILE using DBCC SHRINKFILE (EMPTY).

REMOVE FILE logical_file_name

Removes the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.

Create the file newly and add it to the new filegroup using ALTER DATABASE ADD FILE

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 8

Thanks Everyone...

I feel Steves plan is the best one..

Thanks Gail for pointing that we cannot move file from one file group to another