partition switch does not switch data to the target partition RRS feed

  • Question

  • Hello,

    I'm try to switch partition between two partitioned tables the switch statement works and there are no errors and the data does not switch. Can anyone help with steps to troubleshoot this situation?

    My scenario is something similar to the T-SQL statements below:

    CREATE TABLE [dbo].[TablePartitionSource](
     [ID] [nvarchar](50) NULL,
     [word] [varchar](40) NOT NULL,
     [ProcessingDate] [datetime] NOT NULL
    )  ON [ps_DailySnapshot] ([ProcessingDate])

    CREATE TABLE [dbo].[TablePartitionDestination](
     [ID] [nvarchar](50) NULL,
     [word] [varchar](40) NOT NULL,
     [ProcessingDate] [datetime] NOT NULL
    )  ON [ps_DailySnapshot] ([ProcessingDate])

    Insert INTO [dbo].[TablePartitionSource]  VALUES ((1,'Tom','2016-03-25'),(1,'Dean','2016-03-25'),(1,'Harry','2016-03-25'))

    ALTER TABLE TablePartitionSource SWITCH PARTITION  $partition.pf_DailySnapshot('2016-03-25') TO TablePartitionDestination PARTITION $partition.pf_DailySnapshot('2016-03-25');

    The above switch statement executes successfully but the data won't switch to the empty partition. Does anyone know why>


    Thursday, April 14, 2016 11:21 PM


  • Hi nihcas7713,

    I test the scenario as yours and everything works as expected.

    Firstly, check that if you insert data successfully into your source table by executing “select * from TablePartitionSource”. Please note that the T-SQL statement that insert data should be as follows.

    Insert INTO [dbo].[TablePartitionSource]  VALUES (1,'Tom','2016-03-25'),(1,'Dean','2016-03-25'),(1,'Harry','2016-03-25')

    Secondly, run the following commands to check the details of the partitioned table.

    USE [YourDatabase]
    OBJECT_NAME(idx.object_id) AS TableName ,
    psh.name AS PartitionSchemeName ,
    fnc.name AS PartitionFunctionName,
    part.partition_number AS PartitionNumber ,
    fg.name AS [Filegroup],
    rows AS 'No of Records' ,
    CASE boundary_value_on_right WHEN 1 THEN 'less than'
    ELSE 'less than or equal to' END AS 'Condition',
    value AS 'Range' ,
    part.partition_id AS [Partition Id] FROM sys.partitions part
    JOIN sys.indexes idx
    ON part.object_id = idx.object_id
    AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
    ON psh.data_space_id = idx.data_space_id
    sys.partition_functions fnc
    ON fnc.function_id = psh.function_id LEFT
    JOIN sys.partition_range_values prv
    ON fnc.function_id = prv.function_id
    AND part.partition_number = prv.boundary_id
    JOIN sys.destination_data_spaces dds
    ON dds.partition_scheme_id = psh.data_space_id
    AND dds.destination_id = part.partition_number
    JOIN sys.filegroups fg
    ON dds.data_space_id = fg.data_space_id
    JOIN (SELECT container_id, sum(total_pages) as total_pages
    sys.allocation_units GROUP BY container_id) AS au
    ON au.container_id = part.partition_id JOIN sys.tables t ON
    part.object_id = t.object_id WHERE idx.index_id < 2
    ORDER BY TableName,part.partition_number;

    Thirdly, specify partition number which is returned in above step when switching partition. There is example for your reference. 

    ALTER TABLE TablePartitionSource SWITCH PARTITION  1 TO TablePartitionDestination PARTITION 1;


    Lydia Zhang

    Lydia Zhang
    TechNet Community Support

    Friday, April 15, 2016 6:47 AM