SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning issue


Partitioning issue

Author
Message
stvhull
stvhull
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2430 Visits: 742
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
---------------------------------------------------------------------



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search