partitioning and archiving with both main and archived tables partitioned

• S_Kumar_S

  SSCrazy Eights

  Points: 9037

  More actions

  January 28, 2014 at 9:36 am

  #303832

  HI

  I have a very large partitioned table with 250 partitions each placed on different Filegroup.

  So I have P1,P2,P3.... PLaced on FG2,FG3FG4...

  FG1 in left blank intentionally.Will exaplin down why so.

  Now I have an archive table with similar structure and that is also partitioned. it also uses the same partitioning scheme and filegroups.

  I want to be able to switch in/and out of archive table without any data movement.

  And that is the reson why FG1 is empty.So when I archive the partition1 from main table, FG2 will also be empty. And when I merge 2 empty partition,

  there will be no data movement.

  Now problem is that archive table also uses the same partition scheme and file groups. So mergeing the partition will cause data movement because of this.

  How can I avoid that? Shall I put Archive partitions on different filegroups?

  Will that work?

  thanks

  Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

• S_Kumar_S

  SSCrazy Eights

  Points: 9037

  More actions

  January 28, 2014 at 10:11 pm

  #1684136

  Is it a very uncommon scenario? I am not getting any responses?

  Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.