"Gordon Cowie" <go***@dynamicsdirect.com> wrote in message
news:uz%23WtZzBGHA.676@TK2MSFTNGP10.phx.gbl...
> Just added a new datafile to my database. How do I distribute all the data
> evenly across the two files now?
>
> I tried dbcc dbreindex on all tables, this results in 75%/25%
> I also tried creating 2 new files and emptying the main file dbcc
> shrinkdb. I could not empty the main file completely though so had to
> empty the 3rd file again and drop it.. this gave me a 40%/60%
>
> Anyway to reorganize the datafiles and have 50/50 distribution?

> The cleanest way to ensure even distribution between all files is to export
> the data, truncate the tables and import it back in again. The reason none
> of the techniques worked is that SQL Server adds data to individual files
> within the same file group by the percentage of free space in the file.  So
> if you start with 4 files of the same size and empty all the existing data
> you have 4 files with equal amounts of free space. Then when you insert the
> data it will use the proportional fill algorithm to fill them in in an even
> manor.  But when some of the files already have data in them the
> distribution will be uneven due to the uneven amount of free space.
> Eventually the amount of free space will even out.  So if you run DBCC
> DBREINDEX on all the tables many times it will eventually even out.  The key
> is to ensure all the files are the same size with PLENTY of free space in
> each file.  That way Autogrow does not kick in and mess with the even sizes.
>