creating filegroup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » creating filegroup

creating filegroup

Rate Topic

Display Mode

Topic Options

Author

Message

rajeev-958491

Posted Monday, March 02, 2009 11:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 05, 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

noeld

Posted Wednesday, March 04, 2009 2:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

Take a look at ALTER DATABASE on books online

* Noel

Post #668692

THE-FHA

Posted Thursday, April 28, 2011 7:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

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

John Mitchell-245523

Posted Thursday, April 28, 2011 7:41 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,428, Visits: 7,196

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

THE-FHA

Posted Thursday, April 28, 2011 9:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

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

GilaMonster

Posted Thursday, April 28, 2011 9:38 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:48 AM
Points: 37,712, Visits: 29,967

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

Steve Jones - SSC Editor

Posted Thursday, April 28, 2011 9:56 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,423, Visits: 13,736

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

THE-FHA

Posted Thursday, April 28, 2011 9:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 10:17 PM
Points: 167, Visits: 470

Thanks guys, i will have a look at that with the Adventureworks as practice.

Post #1100367

« Prev Topic | Next Topic »

Permissions