Post reply

How to alter Partition Range on the table

  • February 21, 2012 at 2:21 am

    #265024

    Hi,

    I created the following partition Function on the table

    /* Create Partition on CDR_Account (EDMAccgrpId) */

    CREATE PARTITION FUNCTION pfnAccount (int) AS RANGE LEFT FOR VALUES (

    1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000,10000000)

    CREATE PARTITION SCHEME pscAccount AS PARTITION pfnAccount TO (

    FG1,FG2,FG3,FG4,FG5,FG6,FG7,FG8,FG9,FG10)

    GO

    CREATE TABLE CDR_Account(AccgrpIdbigint IDENTITY,

    EDMAccgrpIdintNOTNULL,

    EdmSrcInfoIdintDEFAULTNULL/* FOREIGN KEY REFERENCES CDR_EDMSrcInfo (EdmSrcInfoId) */,

    AccgrpNumvarchar(20)DEFAULTNULL,

    AccgrpNamevarchar(40)DEFAULTNULL)

    ON pscAccount (EDMAccgrpId)

    ---

    but after Loading 200 mn data in the above table. I realized that only FG1 & FG2 are used and above limit 32GB and 25 GB resp. and other FG's only 1GB. I want to reconfigure data to evenly distribute the range.

    Please help me in this regard. How to create new partition range.

    PS: By mistake I merged the partitions on the table and hence it shows only 2 partitions in Sys.Partitions

    partition_id object_id partiitonNu rows

    7205759403943526438957642601 720575940394352642959503100NONE

    7205759404002508838957642602 720575940400250881247967700NONE

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • February 21, 2012 at 7:01 am

    #1449678

    Assuming you are planning to use the same partition key please check ALTER TABLE SPLIT RANGE syntax, see http://msdn.microsoft.com/en-us/library/ms186307.aspx

    Last but not least... I'm hopping this table is not being partitioned for "performance concerns" as the desired "evenly distribution of rows" may suggest.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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