Post reply

Copy partition data on OLTP 24x7 instance

  • July 3, 2014 at 4:10 am

    #288343

    Hi,

    I am looking to change a number of table partitions from a specific range ie currently 30 day to 15 month. I have created this in test with new Partition Function/Scheme and all works fine. I need to apply this change on tables on a 24x7 OLTP system. There is more data on live than in test so actual copy will take longer and need to be aware of the data copy process and the locks applied.

    Just looking for anyone that has performed this before and what methods/processes they used.

    TIA

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • July 21, 2014 at 10:44 am

    #1731088

    Follow up to my above post, here's my code

    Create Table

    CREATE TABLE TestPartition15m(

    [Id] [BIGINT] NOT NULL,

    [DateCreated] [DATETIME2](3) NOT NULL,

    [Amount] [INT] NOT NULL,

    [SupplierId] [INT] NOT NULL,

    [PartitionId] AS (CONVERT([TINYINT],ABS(DATEDIFF(DAY,CONVERT([DATE],'20000101',(112)),[DateCreated])%(14))+(1),(0))) PERSISTED NOT NULL,

    CONSTRAINT [pk_TestPart] PRIMARY KEY CLUSTERED

    (

    [Id] ASC,

    [PartitionId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [FifteenMonthRetentionScheme]([PartitionId])

    ) ON [FifteenMonthRetentionScheme]([PartitionId])

    GO

    FUnction & Scheme here:

    CREATE PARTITION FUNCTION [FifteenMonthRetention](TINYINT) AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)

    GO

    CREATE PARTITION SCHEME [FifteenMonthRetentionScheme] AS PARTITION [FifteenMonthRetention] TO ([PFGDATA1], [PFGDATA2], [PFGDATA3], [PFGDATA4], [PFGDATA5], [PFGDATA1], [PFGDATA2], [PFGDATA3], [PFGDATA4], [PFGDATA5], [PFGDATA1], [PFGDATA2], [PFGDATA3], [PFGDATA4], [PFGDATA5])

    GO

    I enter data into the table above for last fifteen months, I would expect to see a row in each partition? Any reason why not?

    TIA

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • July 23, 2014 at 3:27 am

    #1731672

    One of those you solve yourself:

    Issue was in the calculation of the Partition ID (computed column)

    [PartitionId] AS CONVERT([TINYINT],DATEDIFF(MONTH,(0),DateCreated)%(16)+(1),(0)) PERSISTED NOT NULL,

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

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

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