March 2, 2009 at 11:12 am
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
March 4, 2009 at 2:15 pm
Take a look at ALTER DATABASE on books online
* Noel
April 28, 2011 at 7:32 am
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.
April 28, 2011 at 7:41 am
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
April 28, 2011 at 9:32 am
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.
April 28, 2011 at 9:38 am
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
April 28, 2011 at 9:56 am
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
April 28, 2011 at 9:59 am
Thanks guys, i will have a look at that with the Adventureworks as practice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply