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 12»»

An Automated Process to Archive Big Tables Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 9:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
Comments posted to this topic are about the item An Automated Process to Archive Big Tables


Post #1471934
Posted Wednesday, July 10, 2013 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:54 AM
Points: 26, Visits: 175
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.
Post #1472203
Posted Wednesday, July 10, 2013 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:43 AM
Points: 4, Visits: 44
why not use partition table to slide out the data?
Post #1472245
Posted Wednesday, July 10, 2013 11:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
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.



Post #1472266
Posted Wednesday, July 10, 2013 11:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
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.



Post #1472269
Posted Wednesday, July 10, 2013 6:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 1:47 PM
Points: 12, Visits: 71
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
Post #1472407
Posted Wednesday, July 10, 2013 8:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:54 AM
Points: 26, Visits: 175
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)....
Post #1472415
Posted Thursday, July 11, 2013 5:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
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.



Post #1472524
Posted Thursday, July 11, 2013 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:54 AM
Points: 26, Visits: 175
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!
Post #1472586
Posted Thursday, July 11, 2013 7:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:47 PM
Points: 12, Visits: 377
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.
Post #1472837
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse