We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.

We decided on the archival since the daily full backup is taking a long time, and so we are planning to keep the archival table in different drive.

We tried the below ways, and all are taking really long:

  • We can’t go for inserting latest data to another table and then rename the tables accordingly because of the foreign key constraints.

  • SSIS package to insert to archive table old data is taking ~6 days in batch insert

  • BCP is also taking ~7- days.

Please suggest any best idea/suggestions considering CDC and foreign keys.

  • 1
    If it's partitioned, why can't you switch out partitions you want to move? – Erik Darling Sep 10 '19 at 17:25
  • Thank you Erik ! Even if we moved the partition to another drive, the daily full backup job will take the same long time. We want to minimize this time – user1424196 Sep 10 '19 at 17:42
  • when you say ssis and bcp takes days - how much data you are inserting ? Is this incremental load or full load everytime ? – Kin Shah Sep 10 '19 at 17:48
  • 1
    If you move it to a read only file group, you could skip it during backups. – Erik Darling Sep 10 '19 at 18:01
  • 1
    Also it sounds, generally, like you've let this database grow beyond the capabilities of your infrastructure, and the procedure you propose won't really change that. – David Browne - Microsoft Sep 10 '19 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.