locked
Remove default partition filegroup RRS feed

  • Question

  • When I first set partitioning on a db, it looked like this:

    CREATE PARTITION FUNCTION PF_XXXX(int) AS RANGE LEFT FOR VALUES (100, 200)

    CREATE PARTITION SCHEME PS_YYYY AS PARTITION PF_XXXX TO (
                [DATA_FG1],
                [DATA_FG2],
                [PRIMARY])

    This was a mistake for that db because Primary has restricted growth. I want instead to have spillover go into [DATA], which has unrestricted growth.

    How do I get [PRIMARY] out of there? If I try

    ALTER PARTITION FUNCTION PF_XXXX MERGE RANGE (200)

    that just replaces [DATA_FG2] with [PRIMARY]. Because there's no range outside of [PRIMARY], I don't know how to force it to merge into a different filegroup.

    I understand that I could always just keep splitting so that there's always another filegroup before [PRIMARY], but ideally I want to get it out of the scheme all together.

    Thanks in advance!

    Tuesday, July 19, 2011 3:51 PM

Answers

  • Hi J.Santos,

    Since with RANGE LEFT partition funcion to merge partitions, SQL Server will move data rightward. In other word, if you merge the last two partitions, the filegroup testFg3 will be removed and the third partition will be allocated at filegroup testFg4:

    ALTER PARTITION FUNCTION MyRangePF1() MERGE RANGE (1000);
    
    {min,1},{2,100},{101,max} (testFg1, testFg2, testFg4)

    If you split the last partition to two by adding the boundary value 1000, the filegroup testFg3 which is removed before will be the NEXT USED filegroup. As a result, SQL Server will still add the filegroup testFg3 to partition 3 and let the filegroup testFg4 at the partition 4.

    ALTER PARTITION SCHEME myRangePS1 NEXT USED testFg3 ;
    ALTER PARTITION FUNCTION MyRangePF1 ()SPLIT RANGE (10000);
    

    {min,1},{2,100},{101,999},{1000,max} (testFg1, testFg2, testFg3,testFg4)

    That is the result based on my research. The filegroup at the last partition seems can’t be changed with RANGE LEFT partition.  In this case, you might need to create a staging table storage the data in your partitioned table and drop the partitioned table along with partition schema and function, then to recreate the partitioned table with right partition function and schema.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, July 26, 2011 9:22 AM
    Thursday, July 21, 2011 8:02 AM

All replies

  • See if the following connect article helps, it includes a method to do this in the comments section...

    http://connect.microsoft.com/SQLServer/feedback/details/331520/unable-to-drop-filegroup-the-filegroup-cannot-be-removed-because-it-is-not-empty

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Wednesday, July 20, 2011 7:16 AM
  • Thanks for the reply.

    In that link, there are two answers from Microsoft. One regards dropping the filegroup: I don't want to drop PRIMARY, I just want to remove it from the partition scheme.

    The other details how to merge the partition function to remove a filegroup from the partition scheme. This is closer to what I want, but still not answering the question.

    The example they give has the following function / scheme:

    CREATE PARTITION FUNCTION MyRangePF1 (int)
    AS RANGE LEFT FOR VALUES (1, 100, 1000);
    GO

    CREATE PARTITION SCHEME MyRangePS1
    AS PARTITION MyRangePF1
    TO (testFg1, testFg2, testFg3, testFg4);

    Their answer then talks of how to remove testFg1 from the scheme by merging. That's fine - I've got a handle on that. What I want to do though is remove testFg4. You'll notice there's no boundary to the right of testFg4, so merging the function never gets rid of it. If I were to do the following:

    ALTER PARTITION FUNCTION MyRangePF1() MERGE RANGE (1);

    ALTER PARTITION FUNCTION MyRangePF1() MERGE RANGE (100);

    ALTER PARTITION FUNCTION MyRangePF1() MERGE RANGE (1000);

    I'd be left with just one filegroup referenced by the scheme - testFg4, which is exactly the one I want to get rid of.

    So the question really is: how to you remove the spillover filegroup (i.e. one with no outside boundary) from a partition scheme?

     


    Wednesday, July 20, 2011 3:44 PM
  • Hi J.Santos,

    Since with RANGE LEFT partition funcion to merge partitions, SQL Server will move data rightward. In other word, if you merge the last two partitions, the filegroup testFg3 will be removed and the third partition will be allocated at filegroup testFg4:

    ALTER PARTITION FUNCTION MyRangePF1() MERGE RANGE (1000);
    
    {min,1},{2,100},{101,max} (testFg1, testFg2, testFg4)

    If you split the last partition to two by adding the boundary value 1000, the filegroup testFg3 which is removed before will be the NEXT USED filegroup. As a result, SQL Server will still add the filegroup testFg3 to partition 3 and let the filegroup testFg4 at the partition 4.

    ALTER PARTITION SCHEME myRangePS1 NEXT USED testFg3 ;
    ALTER PARTITION FUNCTION MyRangePF1 ()SPLIT RANGE (10000);
    

    {min,1},{2,100},{101,999},{1000,max} (testFg1, testFg2, testFg3,testFg4)

    That is the result based on my research. The filegroup at the last partition seems can’t be changed with RANGE LEFT partition.  In this case, you might need to create a staging table storage the data in your partitioned table and drop the partitioned table along with partition schema and function, then to recreate the partitioned table with right partition function and schema.


    Best Regards,
    Stephanie Lv

    • Marked as answer by Stephanie Lv Tuesday, July 26, 2011 9:22 AM
    Thursday, July 21, 2011 8:02 AM