Post reply

Partitioning - moving range

  • August 20, 2008 at 2:43 pm

    #127117

    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

    Pastition2 10/2007 FG1

    Partition3 11/2007 FG1

    Partition4 12/ 2007 FG1

    Partition5 1/2008 FG1

    Partition6 2/2008 FG1

    Partition7 3/2008 FG1

    Partition8 4/2008 FG1

    Partition9 5/2008 FG1

    Partition10 6/2008 FG1

    Partition11 7/ 2008 FG1

    Partiton12 8/2008 Primary (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

    #865000

    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

    Cheers, Max

    Check-out free open-source utility Sql Server Replication Explorer[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply