Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC Shrinkfile empty file not distributing data evenly in primary file group with multiple files Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 5:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
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


  Post Attachments 
Create file group database.txt (3 views, 5.86 KB)
Post #1565848
Posted Tuesday, April 29, 2014 6:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1565879
Posted Tuesday, April 29, 2014 6:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
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?
Post #1565885
Posted Wednesday, April 30, 2014 7:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:49 AM
Points: 57, Visits: 250
Anybody???
Post #1566392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse