An Automated Process to Archive Big Tables

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the item An Automated Process to Archive Big Tables

  • mauriciorpp

    Default port

    Points: 1472

    I was a bit confused in the step that says to rename the table, and later to drop the renamed table. But maybe I missed something, will try to read more carefully later. 🙂

  • y213h

    SSC Rookie

    Points: 43

    why not use partition table to slide out the data?

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    y213h (7/10/2013)


    why not use partition table to slide out the data?

    This is how to do it is you do not have enterprise version of sql server. There are many ways to do the job. This article is concentated mostly how to establish automated archiving after the data is moved out of customer database.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    mauriciorpp (7/10/2013)


    I was a bit confused in the step that says to rename the table, and later to drop the renamed table. But maybe I missed something, will try to read more carefully later. 🙂

    When you rename a table you will be able create an empty table with old name. This trick allows application works and do not interact with records you are archiving. When data is archived it is easier to drop old table instead of deleting millions of rows.

  • Dan Pitta

    SSC Veteran

    Points: 252

    A couple of thoughts..

    Partitioning and BCP seem to work nicely. For the case where partitioning is not setup I like BCP, batching up the process helps too. BCP is nice too since most of the time the active server and the archive server are not the same.

    Instead of creating and dropping table, I like truncate table.

    A few thoughts that might help.

    Dan Pitta

  • mauriciorpp

    Default port

    Points: 1472

    oh, this process is considering we can empty the table then? this is not my case, we need to archive a table where some records are current and can not be deleted. so I'm bound to insert-delete in batches, everyday (with jobs of course).... 🙁

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    mauriciorpp (7/10/2013)


    oh, this process is considering we can empty the table then? this is not my case, we need to archive a table where some records are current and can not be deleted. so I'm bound to insert-delete in batches, everyday (with jobs of course).... 🙁

    This process is not considering to empty table. It considering to keep necessary rows in current table and the rest in archived table in separate database. You can return some records back you need initially. And then, automated process will be running daily/weekly and archive records constantly while keeping necessary number of days in the database table.

  • mauriciorpp

    Default port

    Points: 1472

    Thanks for the reply Leo! I will evalute this method, because our current "insert-delete" takes forever to run every day... thanks for the article!

  • Patrick Ge

    SSC Eights!

    Points: 912

    This is completely nothing to do with the technical discussion here.

    Though you may think partitioning or other methods could do the job better (well, you do understand the version limitation that Leo mentioned) I rekcon the rating of this article should be a 5 instead of a 3 (can't believe it was even lower than 3 before I voted) because of the nice idea and process it shares and methodolic and detailed way how it presents them. Efforts should be recognized and people should be encouraged.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Thanks, for the understanding and a good review. I am always trying to put ideas in my article and present one of the ways to make the task done. Unfortunately, very often I got 4-5 stars for the very simple articles and 3 stars for the articles where process flow, methodology, and architectural decisions are required. I think where many database developers and DBAs have difficulties is proper methodology. You can always replace one SQL statement with another one or one stored procedure code with another code. But it is so difficult maintain ETL processes when they do not have proper methodology and made just to be working without thoughts for the future. Thanks again

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply