sql 05 spread one mdf to multiple ndf

Question

Post reply

sql 05 spread one mdf to multiple ndf

Brian Filppu

Old Hand

Points: 322
More actions
February 15, 2007 at 1:41 pm

#116273

I have a 500 gig sql 2005 database. It is moving over to a new san, 2 new sans actually. It will be set up so that 75% of the data resides on 1 san and 25% on the other. The solution is to divide the data up into 12 files - because it fits this model. Microsoft helped with the design but gave us no clue as how we can evenly spread the data across the files evenly today. I could create the extra ndf files no problem, but I want to go ahead and move the data across the files. The client currently has 1 mdf file. Is the best option to run the shrinkfile with the EMPTYFILE clause? Is there a better way to get all of this data accross these files now?

Thanks

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

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

RML-389850 SSC Rookie Points: 44 More actions · Answer 1

Maybe this is what you need?

http://www.mcse.ms/message2414124.html

Simon Facer SSCertifiable Points: 5427 More actions · Answer 2

How you do it depends on how you want your data spread out ...

If you want to split up your data by table (a table resides in a specific filegroup), then use the CREATE INDEX ... WITH DROP_EXISTING (as is documented in RML's link) to move the table into the filegroup you want.

If you want to split one or more tables across two or more filegroups, you will have to set up partitioning functions. Search for "table partitioning" in BOL 2005 for the details, or just go here for a good overview http://msdn2.microsoft.com/en-us/library/ms345146.aspx.

Brian Filppu Old Hand Points: 322 More actions · Answer 3

I probably did not explain fully what I was trying to do. So i'm not trying to put indexes or certain tables in certain files. I want all data spread across the files. One file group. The client has a dynamics erp package and Microsoft recommended not trying to put indexes or certain tables in certain groups. I believe this is because they don't know the best way to split it up.

The idea was to have your primary file group with 12 files and the data spread across each file. Right now all the data sits in one file so I created an additional 11 files in the primary file group but of course they are emtpy. They will fill up over time as sql does a round robin type of approach to filling it based up the files free space. But, we'd like to get that data spread now to use on the spindles on the 2 sans. That is why i brought up the emtpy file option.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Brian

I suppose one way to do it would be to create twelve new files in a new filegroup. Then you can drop the clustered index of each table and recreate it on the new filegroup (if you have foreign key constraints you will need to drop and recreate these as well). Finally, delete the original filegroup and file.

John

vineet Raut SSC Eights! Points: 813 More actions · Answer 5

Hi Brian,

I think I understand what you are trying to do and think that it is not possible without creating different filegroups.

YOu cannot control on which file wihtin a filegroup your data would reside. But you can control in which Filegroup a data from a particular table would reside.

Hence instead of creating 12 files within a filegroups you will have to create 12 different filegroups and then use partitioning functions to spread the data accordingly in those filegroups.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Just run a DBCC SHRINKFILE EMPTYFILE on the file that contains all the data.

It will not let you completely empty the file, since it contains system objects, but it should move most of the data out to the other files.

vineet Raut SSC Eights! Points: 813 More actions · Answer 7

The DBCC SHRINKFILE EMPTYFILE would only help in removing the data from the current file but it wont help in spreading the data into multiple files. If you need that control you would need to use the Filegroups

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Brian doesn't want to control which objects go to which file. He only wants to ensure that data is split evenly across the files. Having multiple files within the same filegroup is the way to do that.

John