An Automated Process to Archive Big Tables

Question

An Automated Process to Archive Big Tables

Leo Peysakhovich

Hall of Fame

Points: 3973
More actions
July 9, 2013 at 9:28 pm

#295410

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

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

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

mauriciorpp Default port Points: 1472 More actions · Answer 1

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 More actions · Answer 2

why not use partition table to slide out the data?

Leo Peysakhovich Hall of Fame Points: 3973 More actions · Answer 3

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: 3973 More actions · Answer 4

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: 262 More actions · Answer 5

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 More actions · Answer 6

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: 3973 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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: 3973 More actions · Answer 10

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

rrhascall Newbie Points: 1 More actions · Answer 11

Hi Leo,

I know it has been awhile since you posted this article but looking at following your approach for archiving.

I'm confused about the initial steps where you rename the original table to archive and then later re-create the original.

You then "Load X days of data". "The next step is to load X days of data from the original table"

Won't renaming the original table disrupt user access or processes that query this table?

Thanks!