Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-01 : 08:56:27
|
| Kevin writes "If you have a primary file group with one file and make the decision to go to two files because of operating system drive volumes:How does the data get balanced between the two files? Are there any commands that you can look to see how the table is spread across the two operating system files? Is the only way to reorg the database BCP the data out and in? I am talking about a database which is 40-50 gig in size and some tables which have 10 million rows of data.thanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-01 : 09:47:09
|
| If I remember correctly, SQL Server will balance each file in the filegroup depending on the file sizes, for example:FileGroup A: File: A.MDF Size: 200MBFileGroup A: File: B.NDF Size: 100MBFileGroup A: File: C.NDF Size: 100MBThe total filegroup size is 400MB, but since file A.MDF is 50% of the total, 50% of the data would be stored on it, then 25% each on the remaining 2 files. I *think* that this may only apply to newly inserted data, so altering the database to add the new files won't automatically spread the data out.You should be able to do this though: drop the clustered indexes on the tables, add the new files to the filegroups, then re-create the clustered indexes. The clustered index will physically sort the data and should re-distribute it evenly.You might also want to consider separate filegroups for separate tables, instead of sticking with 1 filegroup only. It will give you the same benefits as having multiple files and will also give you finer control over table placement. For example, you can make a special filegroup for the large table(s) with multiple files on different drives, while leaving the smaller tables on a filegroup with one file. |
 |
|
|
|
|
|
Files within Primary Filegroup