Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

default filegroup Expand / Collapse
Author
Message
Posted Tuesday, December 8, 2009 12:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 9, 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
Posted Tuesday, December 8, 2009 5:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:52 AM
Points: 363, Visits: 1,318
Did you, by any chance, create any objects in "PRIMARY"?
Post #830567
Posted Tuesday, December 8, 2009 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 9, 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
Posted Tuesday, December 8, 2009 7:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 2:52 AM
Points: 363, Visits: 1,318
Is there any activity involving the objects from "PRIMARY" - such as table inserts, etc ?
Post #830677
Posted Tuesday, December 8, 2009 8:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
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
Posted Wednesday, December 9, 2009 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 9, 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
Posted Wednesday, December 9, 2009 1:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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
Posted Wednesday, December 9, 2009 1:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 »

Add to briefcase

Permissions Expand / Collapse