reorganize data files

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.
Andrew J. Kelly SQL MVP
"Gordon Cowie" <gordy@.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?|||running dbreindex twice evened it out nicely, thanks!
Andrew J. Kelly wrote:
> The cleanest way to ensure even distribution between all files is to expor
t
> 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. S
o
> 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 th
e
> data it will use the proportional fill algorithm to fill them in in an eve
n
> 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 k
ey
> 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 size
s.
>