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

creating filegroup Expand / Collapse
Author
Message
Posted Monday, March 2, 2009 11:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 5, 2009 12:50 AM
Points: 7, Visits: 54

1) how to create filegroups to distribute large tables over multiple drives and to separate indexes from data



2) How to locate the transaction log on a separate drive or drives from the filegroups that compose the database, and separate key tables from one another
Post #666765
Posted Wednesday, March 4, 2009 2:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
Take a look at ALTER DATABASE on books online


* Noel
Post #668692
Posted Thursday, April 28, 2011 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:10 AM
Points: 180, Visits: 528
No Offence this forumn is to help each other, can somebody come up with a clear solution to this question because i am faced with the same challenge now.
Post #1100226
Posted Thursday, April 28, 2011 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,421, Visits: 10,077
Unfortunately there is no such thing as a clear solution. You need to do your own research based on your own circumstances. Noel has provided a place to start, but there is plenty of other material out there - just use your favourite search engine to find it. If, in the course of your research, you come across something you don't understand, then by all means post back and we'll try to help out. We don't have the time or knowledge of your requirements to do your database design for you, though.

John
Post #1100236
Posted Thursday, April 28, 2011 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:10 AM
Points: 180, Visits: 528
Hi,
I managed to figure out how to overcum this, but my strategy might not be best.
firstly i created a duplicate Database with similar settings on a different instance, and then import the tables to the new db. then on the original db i created a secondary file group together with .ndf, then i drop create a certain table using a script generated by sql management studio, before i ran the script i removed ON [PRIMARY] ans set it to say ON [SECONDARY], ran the script and it ran successfully. I then had to import the original data from the other instance to the original instance, all Data imports where successfull, however at first i expeienced a lot of challenges in terms of Dependencies.
Post #1100339
Posted Thursday, April 28, 2011 9: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 @ 10:20 AM
Points: 40,176, Visits: 36,576
Sounds like a long way around. Can't offer any better option though, since you haven't given details of what you are/were doing.

Do note that new filegroups may not improve performance. If IO was not the bottleneck or the new files are sharing some portion of the IO channel, it won't help at all.



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 #1100348
Posted Thursday, April 28, 2011 9:56 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 10:26 AM
Points: 31,177, Visits: 15,616
First, AFAIK, you cannot separate a table across filegroups. You can separate across files, but not filegroups. The exception is partitioning.

You can move a table's data to a new filegroup by moving the clustered index. The Rebuild section (http://msdn.microsoft.com/en-US/library/ms189858%28v=SQL.90%29.aspx) talks about that. CREATE WITH DROP EXISTING will move to a new filegroup.

ALTER DATABASE is how you move the transaction log. See example F - http://msdn.microsoft.com/en-US/library/ms174269%28v=SQL.90%29.aspx







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1100361
Posted Thursday, April 28, 2011 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 5:10 AM
Points: 180, Visits: 528
Thanks guys, i will have a look at that with the Adventureworks as practice.

Post #1100367
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse