default filegroup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » default filegroup

default filegroup

Rate Topic

Display Mode

Topic Options

Author

Message

v_charalambous

Posted Tuesday, December 08, 2009 12:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 09, 2009 2:58 AM
Points: 4, Visits: 32

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

Post #830450

dmoldovan

Posted Tuesday, December 08, 2009 5:39 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 360, Visits: 1,261

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

Post #830567

v_charalambous

Posted Tuesday, December 08, 2009 6:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 09, 2009 2:58 AM
Points: 4, Visits: 32

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.

Post #830611

dmoldovan

Posted Tuesday, December 08, 2009 7:33 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 360, Visits: 1,261

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

Post #830677

Steve Jones - SSC Editor

Posted Tuesday, December 08, 2009 8:23 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #830719

v_charalambous

Posted Wednesday, December 09, 2009 12:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 09, 2009 2:58 AM
Points: 4, Visits: 32

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?

Post #831203

GilaMonster

Posted Wednesday, December 09, 2009 1:38 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

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 2008, MVP
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

Post #831207

GilaMonster

Posted Wednesday, December 09, 2009 1:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 37,739, Visits: 30,015

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 2008, MVP
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

Post #831211

« Prev Topic | Next Topic »

Permissions