Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

partitioning and archiving with both main and archived tables partitioned Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 9:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:50 AM
Points: 360, Visits: 840
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


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1535537
Posted Tuesday, January 28, 2014 10:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:50 AM
Points: 360, Visits: 840
Is it a very uncommon scenario? I am not getting any responses?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1535706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse