Merge moves the data into the wrong filegroup

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 18, 2015 at 8:03 pm

  #398039

  Hi,

  I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.

  This is my starting point:

  boundary_idBoundary_valuePartitionNumber

  11/09/2012 0:002

  21/10/2012 0:003

  PartitionNumberPartitionRowsFileGroupName

  1799AdtLog_Archive_201208

  2300AdtLog_Archive_201209

  After I switch partition 1 to a staging table I run:

  ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;

  and

  ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');

  I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:

  PartitionNumberPartitionRowsFileGroupName

  1300AdtLog_Archive_201208

  2310AdtLog_Archive_201210

  How do I make sure that the data stays in AdtLog_Archive_201209 file group?

  Thanks.

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 18, 2015 at 10:40 pm

  #1778532

  Found it out: http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/