September 11, 2008 at 1:11 pm
We have added drives to or DB server. I would like to spread the files (or filegroups?) across a few disks. The current single data file is between 8 and 9 gig. We are hoping to keep about 5 years of data in the DB and currently have around 1 year, so the DB will Grow as time goes on.
1: Is it better to add files to the existing primary file group or create a secondary group.
2: Based on what I am reading I am leaning toward a secondary group to separate system data and "user" data tables.
2 a: How do you move files from one file group to another?
September 11, 2008 at 2:25 pm
You could split it that way, but I can't think of what it would gain you. What's the reasoning behind that idea?
If you want to move data from one file group to another, recreate the clustered index on the target file group. Since the clustered index effectively is the table, that'll do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 5:46 am
The main reason for doing this is to spread the data across the new disks and improve/maintain performance as the DB grows.
Would it be better to add files to the existing Primary group and then rebuild the indexes to balance the data across the files?
September 12, 2008 at 5:52 am
joyce.staszewski (9/12/2008)
Would it be better to add files to the existing Primary group and then rebuild the indexes to balance the data across the files?
If you are planning in having a single file group... how are you planning to balance load across the datafiles? :hehe:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 12, 2008 at 6:13 am
I was reading an MSDN article on Files and File groups which indicates that Sql Server uses a "proportional fill strategy. However I just noticed that this if from the "SQL Server 2008 Books Online". I am useing SQL Server 2005 so maybe this doesn't apply?
Another "newbie" question.
Can I/How do I query the system tables to determine how full and data file is?
September 12, 2008 at 6:22 am
That's correct but remember SQL Server does not know how your application uses tables and indexes... even if space would be allocated across all datafiles you may end with all heavily used objects in a single datafile -defeating this way the purpose of load balancing.
I personally prefer to have several file groups; that way I give myself a chance to fix load balancing issues on my own.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply