Split database files into multiple files in same FILEGROUP

Question

Split database files into multiple files in same FILEGROUP

Electrix

SSC Enthusiast

Points: 115
More actions
January 28, 2010 at 5:08 am

#156619

I have a DB of 300GB with a single datafile. I want to split the file into more than 1 file, but I cannot have different filegroups. How can I achieve this. Ultimately I would like to move all the Indexes to a separate file. How can I achieve this?
(Just to clarify, How can I move some of the data in the existing datafile to another datafile without creating different filegroups?)
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

If you want to define which object goes to which file, specifying new filegroups is the only way.

If files are all part of the same filegroup SQL will just spread data across them in a round-robin fashion, it won't be by table or index.

---------------------------------------------------------------------

Electrix SSC Enthusiast Points: 115 More actions · Answer 2

george sibbald (1/28/2010)
If you want to define which object goes to which file, specifying new filegroups is the only way.
If files are all part of the same filegroup SQL will just spread data across them in a round-robin fashion, it won't be by table or index.

Thanks SSCrazy. Do you know if there is any way to force SQL to write to the new file? What if I change the maxfile size of the original database fileto the current size so that there is no more growth available. Will the new datafile then be used, or am I going to run into issues?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

SQL fills files on a proportional fill basis. i.e it tries to keep all files in a filegroup equally full, which means it would tend to write more to a file with more free space.

If you cap one of the files it will stop writing to that file when full and use the others, so you can have an effect. However when data is deleted/updated space could become free in the capped file, or when you defragment an index data could be moved about, so you cannot guarantee where data will end up.

the only way to be 100% sure is to use filegroups, this is the accepted method. Not sure why you cannot have more than one filegroup?

---------------------------------------------------------------------

Electrix SSC Enthusiast Points: 115 More actions · Answer 4

I understand that using filegroups are the correct method but it is a propriety system that we have no control over.

Thanks for your help. I wish there was a way to move some of the 300GB data to the new file.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

if the disk is raided create a new file, it will fill, you will still see a performance benefit.

If you can have the file on a different drive will see even more performance improvements

---------------------------------------------------------------------

Electrix SSC Enthusiast Points: 115 More actions · Answer 6

Electrix

SSC Enthusiast

Points: 115

January 28, 2010 at 7:05 am

#1109964

My idea is to create the file on another LUN