Switching Out Table Partition to a different file group

Question

Post reply

Switching Out Table Partition to a different file group

paul 1171

Valued Member

Points: 73

April 25, 2018 at 1:04 am

#330524

Hi.

I'm trying to switch out partition 2 on table1 to staging_table.
The staging table is "Secondary" file group.

ALTER TABLE table1 SWITCH PARTITION 2 TO staging_table

But I get this error

ALTER TABLE SWITCH statement failed. index 'DB_AAU.dbo.staging_charge_detail_call_20180425-162517.staging_charge_detail_call_20180425-162517_IX_charge_main' is in filegroup 'Secondary' and partition 2 of index 'DB_AAU.dbo.charge_detail_call.IX_charge_main' is in filegroup 'Third'.

object	p#	filegroup	rows	comparison	value
table1	1	Third	0	less than or equal to	722272275
table1	2	Third	102086039	less than or equal to	865138236
table1	3	Third	2500000	less than or equal to	867638236
table1	4	Fifth	44264272	less than or equal to	912638236
table1	5	Third	228641887	less than or equal to	NULL

Thanks,
Paul

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

From BOL: "Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. "

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

paul 1171 Valued Member Points: 73 More actions · Answer 2

Thanks, yeah I did it a while ago on the same file group took only 1 to 2 seconds to transfer data from one table to another.

I'm trying to a split on the partition but the upper boundary has already been reached and there are around 228641887 rows on the 5th partition.

I ran the split for 15 mins but it did not complete. How long should I wait ?

My VM has SSDs on it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

paul 1171 - Wednesday, April 25, 2018 2:30 AM
Thanks, yeah I did it a while ago on the same file group took only 1 to 2 seconds to transfer data from one table to another.
I'm trying to a split on the partition but the upper boundary has already been reached and there are around 228641887 rows on the 5th partition.
I ran the split for 15 mins but it did not complete. How long should I wait ?
My VM has SSDs on it.

I'm sorry, I can't answer this - we've not had to do a split on our data. Bearing in mind that the data has to be sorted and some of it has to be physically moved, it's not going to be instant, like switching.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden