SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


creating filegroup


creating filegroup

Author
Message
rajeev-958491
rajeev-958491
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12284 Visits: 2048
Take a look at ALTER DATABASE on books online


* Noel
FTdenali
FTdenali
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 572
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.
John Mitchell-245523
John Mitchell-245523
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18350 Visits: 16178
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
FTdenali
FTdenali
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 572
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113743 Visits: 45436
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, 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


Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81344 Visits: 19205
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
My Blog: www.voiceofthedba.com
FTdenali
FTdenali
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 572
Thanks guys, i will have a look at that with the Adventureworks as practice.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search