April 29, 2014 at 5:00 am
Could somebody help me tell me why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
Please could somebody tell me if there is a way to get the data distributed evenly over all three files? Am I doing something wrong here.
Again the script is attached to recreate this:
Thanks
April 29, 2014 at 6:19 am
I doubt if we can control this. As page split and data insertion is not always even.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 29, 2014 at 6:31 am
This looks exactly even though. It has put 20MB in one file and the other 20MB in the other two files. Is this part of the algorithm that SQL Server uses to split data, i.e. 50% on initial file and 50% on any remaining files?
April 30, 2014 at 7:36 am
Anybody???
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply