1

I am trying to implement switch partitioning on one of the tables and I made sure that the partition function,scheme,file groups are working fine. But I get the file group error when I run the below command. Can someone share your thoughts on this.

Command :-

ALTER TABLE XYZ SWITCH PARTITION  5 TO ABC PARTITION  5;

Error :-

ALTER TABLE SWITCH statement failed. table 'XYZ' is in filegroup 'PRIMARY' and partition 5 of table 'ABC' is in filegroup 'FG_5'.

Siyual
  • 16,415
  • 8
  • 44
  • 58
Teja
  • 13,214
  • 36
  • 93
  • 155

3 Answers3

0

Some cluster index might be created on the existing tables . So file group is mentioned during creation of cluster index on the tables that might be different. or so if you can delete the ABC table and create again and try your query.

Query to check the filegroup of the table and index names.

select f.name,o.name,i.name from sys.indexes i inner join  sys.filegroups f on i.data_space_id=f.data_space_id
 inner join sys.all_objects o on o.object_id= i.object_id 
 where o.name in ('ABC','XYZ')

I won't say this is solution but this should help in solving the problem.

Rohit Padma
  • 603
  • 5
  • 15
  • One thing what I am doing is before doing ALTER SWITCH PARTITION I am dropping the clustered column store index on the source table. – Teja Jul 14 '17 at 20:18
  • Can you run the query i mentioned to see the filegroup associated with your tables? did you try dropping the target table and create a new one and try your query ? – Rohit Padma Jul 14 '17 at 20:22
0

The error indicates that the source and target tables are not storage aligned. Run the query below to make sure the source and target filegroups are identical both the table and indexes:

SELECT
    OBJECT_NAME(p.object_id) AS ObjectName, 
    i.name AS IndexName, 
    p.index_id AS IndexID, 
    ds.name AS PartitionScheme, 
    p.partition_number AS PartitionNumber, 
    fg.name AS FileGroupName, 
    prv_left.value AS LowerBoundaryValue, 
    prv_right.value AS UpperBoundaryValue, 
    CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
    p.rows AS Rows
FROM
    sys.partitions AS p INNER JOIN
    sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
    sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id INNER JOIN
    sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id INNER JOIN
    sys.partition_functions AS pf ON pf.function_id = ps.function_id INNER JOIN
    sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number INNER JOIN
    sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id LEFT OUTER JOIN
    sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT OUTER JOIN
    sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE
    p.object_id IN (
        OBJECT_ID(N'dbo.ABC') 
        , OBJECT_ID(N'dbo.XYZ')
    )
    AND p.partition_number = 5
ORDER BY
     ObjectName
    ,IndexName
    ,PartitionNumber;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • No rows are returned when I run this query... Infact i removed the p.object_id part but still there are no rows returned... – Teja Jul 14 '17 at 22:55
  • I am actually creating clustered columnstore index on source table and then dropping it before doing switch partitioning... – Teja Jul 14 '17 at 23:13
  • @Teja, is the clustered columnstore index partitioned? I update the query to return both partitioned and non-partitioned tables/indexes. – Dan Guzman Jul 15 '17 at 01:45
  • Hi Dan - The source table is not partitioned but I am creating a clustered columnstore index and dropping it after that.. once the index is dropped I am doing the alter table switch partition. – Teja Jul 15 '17 at 01:48
  • Why to you specify partition 5 as the source if the table isn't partitioned? If the source is a non-partitioned heap and the target is a partitioned heap, you need to make sure the source table is on filegroup FG_5 and has a check constraint to match the target partition boundaries. – Dan Guzman Jul 15 '17 at 03:06
  • I have 40 different slice tables and I would like to do a parallel write into the partitioned table... The 40 could be any number... Instead of doing parallel write which is slow.. I wanted to do Switch Partitioning... My source table is in PRIMARY FG and the target partition table is spread across multiple file groups separated by different drives.. I don’t have control on the source table file group... Is there any way to implement Alter Table Switch Partition with my current situation... – Teja Jul 15 '17 at 03:11
  • @Teja, the source table must be on the same filegroup as the target partition in order to use `SWITCH` so the answer is no, you cannot use `SWITCH` in your current situation. – Dan Guzman Jul 15 '17 at 03:18
  • How can I change the file group of my source table once it is already created or even before its created... Also there are 40 different source tables and all of them should be in the respective destination table FG to do the SWITCH as per your previous comment... – Teja Jul 15 '17 at 03:21
  • @Teja, you can specify the `MOVE TO` option when dropping the clustered columnstore index to move it to the same filegroup as the target partition during the drop (e.g. `ALTER INDEX YourColumnstoreIndex ON TABLE dbo.SourceTable WITH (MOVE TO FG_5_);`). You mentioned earlier you have no control over the source table filegroup so I'm still unclear. – Dan Guzman Jul 15 '17 at 12:31
  • Okay let me try out the above option you have specified and get back here.... thank u so much for ur help... – Teja Jul 17 '17 at 02:22
-1

Check your partitionscheme. Both tables should be in the same filegroup. You should involve PRIMARY in you partition scheme or move the SOURCE table to the destination FILEGROUPS that the destination table will be using.

michel Carlo Sliwa
  • 1