Multiple primary filegroups

Question

Multiple primary filegroups

lostdba

Old Hand

Points: 341
More actions
March 10, 2006 at 9:59 am

#365557

Hi, I'm wondering if someone could help me. I just inherited a db with multiple primary filegroups and was wondering if this would be a problem.
Thanks
March 13, 2006 at 8:00 am

This was removed by the editor as SPAM

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

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

skeane SSC Eights! Points: 877 More actions · Answer 1

I don't understand what you mean by multiple primary filegroups. By definition there is just one primary filegroup - the one with the system tables. How have you determined that you have multiple primaries?

Dave Foster SSC Veteran Points: 235 More actions · Answer 2

Correct me if I am wrong, but I am assuming you mean that you have multiple physical files tied to the primary file group. Is this correct?

In my experience this can cause slower response if the different physical files are on the same physical disk. Similar to the problem you might get if you let a database autogrow in very small chunks. This creates fragmentation.

If this is what you are experiencing sql2000 has the ability to move data from one physical part of the primary filegroup to another. It is located in the shrink database menu in enterprise manager. Once all of the information has been removed from the physical file you should be able to delete it without losing data. Be careful though due to the amount of time this may require.

lostdba Old Hand Points: 341 More actions · Answer 3

Sorry for the confusion, I guess the best way to explain it is if you go to the database properties and look at filegroups there are 3 primary files and if you look at the datafiles tab, there are 3 different file names.

Thanks

Charles E. Rimpila Newbie Points: 3 More actions · Answer 4

I also have inherited a db with multiple files under the Primary filegroup. Basically, I have two files with 250 Mb allocated to them. Both of them are associated as the Primary file group. Both files are located on the same drive of a local PC. It's a relatively small database. The situation I have run into, is one of the files has maxed out the 250Mb allocated to it while the other still has 140Mb free space. Is this potentially causing performance issues? We've noticed a decrease in speed working with it, but have yet to pinpoint it solely on db performance.

How does MS SQL handle working with tables and indexes spread from one file to the second? I realize that this is not an optimal setup. I have been told by others that there should be no worries as the second file in the Primary file group will autogrow when it starts to max out. The first file has not. Should I be worried about this? Any recomendations to handle this?

Thanks.

Gigabyte Guru,

Charlie Rimpila