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

Partitioning issue Expand / Collapse
Author
Message
Posted Tuesday, August 18, 2009 8:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:42 AM
Points: 1,637, Visits: 650
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
72057594038321152 2073058421 1 1 72057594038321152 82
72057594038386688 2073058421 1 2 72057594038386688 28
72057594038452224 2073058421 1 3 72057594038452224 90

Count minid maxid mindt maxdt part. filegroup
82 1 82 2008-11-11 00:00:01 2009-01-31 00:00:01 1 fg1
28 83 110 2009-02-01 00:00:01 2009-02-28 00:00:01 2 fg2
90 111 200 2009-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
72057594038321152 2073058421 1 1 72057594038321152 82
72057594038386688 2073058421 1 2 72057594038386688 28
72057594038452224 2073058421 1 4 72057594038452224 59
72057594038517760 2073058421 1 3 72057594038517760 31

Count minid maxid mindt maxdt part. Filegroup
82 1 82 2008-11-11 00:00:01 2009-01-31 00:00:01 1 fg1
28 83 110 2009-02-01 00:00:01 2009-02-28 00:00:01 2 fg2
31 111 141 2009-03-01 00:00:01 2009-03-31 00:00:01 3 fg4
59 142 200 2009-04-01 00:00:01 2009-05-29 00:00:01 4 fg3
---------------------------------------------------------------------



Post #772731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse