locked
Partitioning and Archiving RRS feed

  • Question

  • Hi, Currently we are archiving the old data using traditional batched deletes which is causing severe blocking as the table is highly transactional. so we are planning to use partitioning. our plan is to archive data to a different archive database. Looks like we can't directly switch the partition between different databases. Whats the best strategy here? I worked on partitioning earlier where I just deleted the older data which is like switch the partition to an empty switch table and truncate that table. But here I need to send all the old data to different archive table. What options do we have here? Any thoughts are appreciated. THanks!

    Thursday, June 23, 2016 6:49 PM

Answers

  • Well if you really need the archive in a separate database, you really have no choice.  You can always just leave archive tables in the main database.

    Once the data is switched out you can copy it to the archive database over time, limiting the resources it uses.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by SQLmaddy Thursday, June 23, 2016 7:22 PM
    Thursday, June 23, 2016 7:15 PM

All replies

  • Switch the data out to a staging table and then copy it to the other database over time.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, June 23, 2016 6:51 PM
  •  Thought about it but with all the GUIDs in the table it generates so much IO. we have around 30k writes\minute and we are scared that manual copy to archive db may slow down the peformance. Have to test that though. 
    Thursday, June 23, 2016 7:09 PM
  • Well if you really need the archive in a separate database, you really have no choice.  You can always just leave archive tables in the main database.

    Once the data is switched out you can copy it to the archive database over time, limiting the resources it uses.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by SQLmaddy Thursday, June 23, 2016 7:22 PM
    Thursday, June 23, 2016 7:15 PM
  • You can bcp data from your staging table to the dat file and then bcp dat file to the table in your different database table.

    A Fan of SSIS, SSRS and SSAS

    Thursday, June 23, 2016 7:20 PM
  • yeah, looks like that is the only option. Thanks a lot for such a quick reply! 
    Thursday, June 23, 2016 7:22 PM
  • Thanks, yeah possibly exploring multiple options here: 

    1. Batched insert from the staging table

    2. BCP\Bulk Insert

    3. schedule SSIS job

     Actually I asked the question to find out if we can do the same directly with the partitioning feature.

    Thursday, June 23, 2016 7:39 PM