Post reply

default filegroup

  • December 8, 2009 at 12:10 am

    #214367

    Hi,

    I have a database with 2 Filegroups and a log file. The filegroups are named PRIMARY and

    Data FileGroup1. The strange thing is that while Data FileGroup1 is set as default the PRIMARY filegorup is increasing. For example. the size of PRIMARY is 900MB whereas Data FileGroup1 is 40 MB. I want this to be the other way round. PRIMARY 40 MB and Data FileGroup1 900MB .

    Can anyone please explain why this is hapenning and how to reverse the filegroups?

    Regards,

    Vasilis

  • December 8, 2009 at 5:39 am

    #1089130

    Did you, by any chance, create any objects in "PRIMARY"?

  • December 8, 2009 at 6:44 am

    #1089169

    Yes i did. but when i create new objects now they go on the Data FileGroup1. So probably i added the Data FileGroup1 after i added objects on the Primary.

  • December 8, 2009 at 7:33 am

    #1089228

    Is there any activity involving the objects from "PRIMARY" - such as table inserts, etc ?

  • December 8, 2009 at 8:23 am

    #1089267

    Objects do not cross filegroups. If there is data being added to objects that were created in PRIMARY, that filegroup will grow. To move those objects to filegroup1,

    - For tables, rebuild the clustered index on filegroup1

    - for indexes, rebuild them on filegroup1

    - for stored procs/UDFs, drop and recreate on filegroup1.

  • December 9, 2009 at 12:47 am

    #1089674

    Well, I have around a hundred tables using the Primary file group with data.

    Thanks for your help. How do i mark the post as closed?

  • December 9, 2009 at 1:38 am

    #1089677

    v_charalambous (12/9/2009)

    Thanks for your help. How do i mark the post as closed?

    You don't. Don't worry about it.

    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

  • December 9, 2009 at 1:43 am

    #1089680

    Steve Jones - Editor (12/8/2009)

    - for stored procs/UDFs, drop and recreate on filegroup1.

    Stored procs, views and functions cannot have a filegroup specified. They're always stored inside the system tables which are always on the primary filegroup

    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

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

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