locked
Partition Switching for multiple months/partition data RRS feed

  • Question

  • Hello,

    We implemented Monthly LEFT RANGE PARTITIONING on table and created created partitions till '2015-01-01'. Now the data got populated till 2017-FEB it was inserted into last partition '2015-01-01'. I am trying Partition switch to move the data to empty table to make the last partition empty.  Data from last partition moved to empty table.

    I created missing partitions on table and now want to restore data from TEMP Table ev_log_table_copy to partitioned table. but getting below error:

    Msg 4982, Level 16, State 1, Line 1
    ALTER TABLE SWITCH statement failed. Check constraints of source table 'sbdw221.ev_log_table_copy' allow values that are not allowed by range defined by partition 37 on target table 'sbdw221.ev_log_table'.

    How do we switch IN/OUT with multiple partition data ?

    CREATE TABLE [sbdw221].[ev_log_table]( [ID] [int] IDENTITY(1,1) NOT NULL, [EVENT_TYPE] [nvarchar](256) NOT NULL, [CLASSIFIER] [nvarchar](256) NULL, [EVENT_TIME] [datetime] NOT NULL ) ON [dwh_data001] TEXTIMAGE_ON [dwh_data001]; CREATE PARTITION FUNCTION PF_MONTHLY_PP (DATETIME) AS RANGE LEFT FOR VALUES ('2014-01-01') CREATE PARTITION SCHEME PS_MONTHLY_PP AS PARTITION PF_MONTHLY_PP ALL TO (dwh_data001) ALTER PARTITION SCHEME PS_MONTHLY_PP NEXT USED dwh_data001 ALTER PARTITION FUNCTION PF_MONTHLY_PP() SPLIT RANGE ( '2014-12-01' ) Create Unique Clustered Index IX_ev_log_table On [sbdw221].[ev_log_table](EVENT_TIME, ID) On PS_MONTHLY_PP(EVENT_TIME) -- Swtiching data from last partition to non partitioned table. ALTER TABLE [sbdw221].[ev_log_table] SWITCH PARTITION 37 TO [sbdw221].[ev_log_table_copy];

    ALTER TABLE [sbdw221].[ev_log_table_copy] ADD CHECK  ([EVENT_TIME] > '2015-01-01 00:00:00.000' and [EVENT_TIME] <='2017-03-01 00:00:00.000')
    ALTER TABLE [sbdw221].[ev_log_table_copy]  SWITCH TO [sbdw221].[ev_log_table]  PARTITION 37



    Sachin Butala,System administrator

    Friday, February 10, 2017 5:02 PM

Answers

  • It seems you've created monthly partitons from '2015-01-01' through '2017-03-01' but have all those months in a single partition in the copy table. Rather than using a check constraint, partition the sbdw221.ev_log_table_copy table using the same scheme as the target table and then switch in the monthly partitions. Below is an example.

    CREATE UNIQUE CLUSTERED INDEX IX_ev_log_table_copy
    			ON [sbdw221].[ev_log_table_copy](EVENT_TIME, ID) 
    			WITH(DROP_EXISTING=ON)
    			ON PS_MONTHLY_PP(EVENT_TIME);
    
    DECLARE @month datetime = '20150201';
    WHILE @month <='20170301'
    BEGIN
    	ALTER TABLE [sbdw221].[ev_log_table_copy]
    		SWITCH PARTITION $PARTITION.PF_MONTHLY_PP(@month)
    		TO [sbdw221].[ev_log_table] PARTITION $PARTITION.PF_MONTHLY_PP(@month);
    	SET @month = DATEADD(month, 1, @month);
    END;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Sachinonnet Wednesday, February 15, 2017 9:40 AM
    Friday, February 10, 2017 7:48 PM
    Answerer

All replies

  • Run the query below to verify partition 37 is returned:

    SELECT $PARTITION.PF_MONTHLY_PP('2015-01-01 00:00:00.003'), $PARTITION.PF_MONTHLY_PP('2017-03-01 00:00:00.000');

    If 37 is returned, verify the check constraint is trusted.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 10, 2017 5:15 PM
    Answerer
  • It returns 

    SELECT $PARTITION.PF_MONTHLY_PP('2015-01-01 00:00:00.003'), $PARTITION.PF_MONTHLY_PP('2017-03-01 00:00:00.000');

    37   62


    Sachin Butala,System administrator

    Friday, February 10, 2017 5:56 PM
  • It seems you've created monthly partitons from '2015-01-01' through '2017-03-01' but have all those months in a single partition in the copy table. Rather than using a check constraint, partition the sbdw221.ev_log_table_copy table using the same scheme as the target table and then switch in the monthly partitions. Below is an example.

    CREATE UNIQUE CLUSTERED INDEX IX_ev_log_table_copy
    			ON [sbdw221].[ev_log_table_copy](EVENT_TIME, ID) 
    			WITH(DROP_EXISTING=ON)
    			ON PS_MONTHLY_PP(EVENT_TIME);
    
    DECLARE @month datetime = '20150201';
    WHILE @month <='20170301'
    BEGIN
    	ALTER TABLE [sbdw221].[ev_log_table_copy]
    		SWITCH PARTITION $PARTITION.PF_MONTHLY_PP(@month)
    		TO [sbdw221].[ev_log_table] PARTITION $PARTITION.PF_MONTHLY_PP(@month);
    	SET @month = DATEADD(month, 1, @month);
    END;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Sachinonnet Wednesday, February 15, 2017 9:40 AM
    Friday, February 10, 2017 7:48 PM
    Answerer
  • Thanks Dan,

    It worked I am able to move 10 millions of rows in less than 10 minutes, where as creating partition without SWITCH IN/OUT takes more than 7 days.



    Sachin Butala,System administrator

    Wednesday, February 15, 2017 9:42 AM
  • I'm glad you were able to fix you partition boundaries in a reasonable amount of time. I expect most of the 10 minutes was due to the partitioned index create since SWITCH typically takes only a few seconds.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, February 15, 2017 1:42 PM
    Answerer