August 20, 2008 at 2:43 pm
I am planning on partitioning a big table. the objective is to have data older than 1 year on a different file group than the rest of the data.
so here is what i am thinking:
Partition boundry Filegroup
Partition 1<= 09/ 2007 FG_2
Pastition210/2007FG1
Partition3 11/2007FG1
Partition4 12/ 2007FG1
Partition5 1/2008FG1
Partition6 2/2008FG1
Partition7 3/2008FG1
Partition8 4/2008FG1
Partition9 5/2008FG1
Partition106/2008FG1
Partition117/ 2008FG1
Partiton128/2008Primary (with the rest of my database)
Partition13 empty
When September rolls around, I want October of 2007 to move to FG2
Then I want August data to go to FG1
And septemeber data to be on primary…
what is happening when I merge partition1 and partition2, is that they both get to be on FG1, freeing FG2. but my goal is to do the oposit, I want to merge the 2 partitions, but I need them to stay on FG2
Anyone has any advice on how to acomplish that?
August 29, 2008 at 7:35 am
From MSDN on ALTER PARTITION ...MERGE , http://msdn.microsoft.com/en-us/library/ms186307(SQL.90).aspx
The filegroup that originally held boundary_value is removed from the partition scheme unless it is used by a remaining partition, or is marked with the NEXT USED property. The merged partition resides in the filegroup that originally did not hold boundary_value.
Which means that the partition1 must contain the boundary value that you are passing to MERGE(..) operator.
In your case, before running MERGE have you tried this:
ALTER PARTITION SCHEME [YourScheme] NEXT USED FG_2
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply