Hi ,
I am testing approaches for balancing data amongs the datafiles of the DB. We have a SQL MI GP data base. there was only one datafile for the db.
There is only one table in the db, and it has a clustered index . There are close to 800,000 rows on the table.
we added one more data file i.e a secondary data file .
Both files are sized the same with identical growth increment values and both files are on the Primary file group.
And to balance the data between the 2 data files, we attempted the following:
1) Rebuild the Clustered Index ci of the tables:
This shows only little data movement.
2) Added a new filegroup and moved the clusteredindex to the new fg and then transsferred it back into the Primary file group.
This shows better movement than 1) but nowhere close to equal .
If i run dbcc showfilestats , the mdf file has around 5000 used extents and the secondary data file has around 300 used extents.
this is nowhere close to even.
Please can anyone suggest what might be going on here? or is there no definite way to redistribute data evenly between the data files of a sql db?
thanks