Switching Out Table Partition to a different file group

• paul 1171

  SSC Enthusiast

  Points: 103

  More actions

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 25, 2018 at 2:08 am

  #1988150

  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

  SSC Enthusiast

  Points: 103

  More actions

  April 25, 2018 at 2:30 am

  #1988152

  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

  April 25, 2018 at 3:42 am

  #1988157

  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