Partitioning issue

• stvhull

  Hall of Fame

  Points: 3700

  More actions

  August 18, 2009 at 8:02 am

  #213687

  I have inherited a table partitioning scheme (using SQL Server 2005), in which I just noticed some unexpected behavior (although it may have been like this all along). Basically, we are intending to keep data partitioned by month, each on its own file group. For example:

  January data (and earlier) in fg1

  February data in fg2

  March data (and beyond) in fg3

  Then, on a monthly basis, we would like to remove and drop the first partition and filegroup (in this case fg1) and create and populate a new partition and filegroup (in this case, fg4).

  The unexpected behavior we are encountering is this: when creating and ‘splitting’ into filegroup fg4, we expect that April data would go in there and that March data would remain in filegroup fg3. However we are seeing just the opposite; the April data is going into filegroup fg3 and the March data is going into filegroup fg4. I guess this behavior is consistent with how the partition scheme looks after we alter it to add fg4 (fg1, fg2, fg4, fg3), but why doesn’t fg4 get added to the end? Is this to be expected? Is it a bug? Are we doing something wrong in our process?

  Below is a sample script to simulate this, with some sample output sprinkled in.

  use master

  go

  drop database partitiontest

  create database partitiontest

  on primary

  (name = db_dat, filename = 'R:\MNT-GEN-1\MNT-SQL-DBA-SH\DATA\db.mdf', size = 2mb),

  filegroup fg1

  (name = fg1_dat, filename = 'R:\MNT-GEN-1\MNT-SQL-DBA-SH\DATA\fg1.ndf', size = 2mb),

  filegroup fg2

  (name = fg2_dat, filename = 'R:\MNT-GEN-1\MNT-SQL-DBA-SH\DATA\fg2.ndf', size = 2mb),

  filegroup fg3

  (name = fg3_dat, filename = 'R:\MNT-GEN-1\MNT-SQL-DBA-SH\DATA\fg3.ndf', size = 2mb)

  log on

  (name = db_log, filename = 'U:\MNT-GEN-1\MNT-SQL-DBA-SH\log.ldf', size = 2mb);

  go

  use partitiontest

  go

  create partition function partfunc (datetime) as

  range left for values ('2/1/09 00:00:00','3/1/09 00:00:00');

  create partition scheme partscheme as

  partition partfunc to ([fg1],[fg2],[fg3]);

  create table t1

  (id int

  ,dt datetime

  ,constraint ci_t1_id primary key clustered(id,dt))

  on partscheme(dt);

  set nocount on

  declare @i int

  declare @dt datetime

  set @i = 1

  set @dt = '11/11/08 00:00:01'

  while @i <=200
  begin
  insert t1 (id,dt) select @i, @dt

  set @dt = dateadd(dd,1,@dt)

  set @i = @i + 1

  end

  go

  -- checking queries

  select * from sys.partitions where object_id = object_id('t1')

  select count(*) as count, min(id) as minid, max(id) as maxid, min(dt) as mindt, max(dt) as maxdt, $partition.partfunc(dt) as partition, ds.name as filegroup

  from t1

  join sys.destination_data_spaces dds on dds.destination_id = $partition.partfunc(dt)

  join sys.data_spaces ds on dds.data_space_id = ds.data_space_id

  group by $partition.partfunc(dt), ds.name

  ----------------------------------------

  RESULTS:

  Partition_id object_id partition_id rows

  720575940383211522073058421117205759403832115282

  720575940383866882073058421127205759403838668828

  720575940384522242073058421137205759403845222490

  Count minid maxid mindt maxdt part. filegroup

  821822008-11-11 00:00:01 2009-01-31 00:00:01 1 fg1

  28831102009-02-01 00:00:01 2009-02-28 00:00:01 2 fg2

  901112002009-03-01 00:00:01 2009-05-29 00:00:01 3 fg3

  -------------------------------------------------

  ALTER DATABASE partitiontest ADD FILEGROUP [fg4]

  ALTER DATABASE partitiontest ADD FILE (name = fg4_dat, filename = 'R:\MNT-GEN-1\MNT-SQL-DBA-SH\DATA\fg4.ndf', size = 2mb) TO FILEGROUP [fg4]

  alter partition scheme partscheme next used [fg4];

  alter partition function partfunc() split range ('4/1/09 00:00:00');

  -- checking queries

  select * from sys.partitions where object_id = object_id('t1')

  select count(*) as count, min(id) as minid, max(id) as maxid, min(dt) as mindt, max(dt) as maxdt, $partition.partfunc(dt) as partition, ds.name as filegroup

  from t1

  join sys.destination_data_spaces dds on dds.destination_id = $partition.partfunc(dt)

  join sys.data_spaces ds on dds.data_space_id = ds.data_space_id

  group by $partition.partfunc(dt), ds.name

  -------------------------------------------

  RESULTS:

  Partition_id object_id partition_id rows

  720575940383211522073058421117205759403832115282

  720575940383866882073058421127205759403838668828

  720575940384522242073058421147205759403845222459

  720575940385177602073058421137205759403851776031

  Count minid maxid mindt maxdt part. Filegroup

  821822008-11-11 00:00:01 2009-01-31 00:00:01 1 fg1

  28831102009-02-01 00:00:01 2009-02-28 00:00:01 2 fg2

  311111412009-03-01 00:00:01 2009-03-31 00:00:01 3 fg4

  591422002009-04-01 00:00:01 2009-05-29 00:00:01 4 fg3

  ---------------------------------------------------------------------