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

data archiving Expand / Collapse
Author
Message
Posted Tuesday, August 26, 2008 11:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:03 AM
Points: 79, Visits: 211
that mean I need to go thru' trial-and-error? Thanks anyway
Post #559339
Posted Wednesday, August 27, 2008 6:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
Heh... no... trust me... I just got done reducing/archiving a 131 million row table to a 44 million row table using the 20k method I spoke of... on a 24/7 system. ;) I don't make recommendations unless I've actually done them (and the related trial and error).

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560047
Posted Wednesday, August 27, 2008 10:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:03 AM
Points: 79, Visits: 211
Great, you looks confident! how you do partitioning switching? dynamic queries? manual? how often? weekly? monthly?
using the 20k method? what 20k method?
Post #560114
Posted Thursday, August 28, 2008 6:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
Depends on what your partitions are based on. Mine are based on a "CampaignID"... any campaign that hasn't had a new row added to it in 90 days gets archived. I just do a select/group by on the CampaignID and the Max LoadDate vs GETDATE() and loop through the campaigns to do the Insert/Delete's. It's actually a double loop... inside loop does the archival insert/delete 20K rows at a time as previously determined by divided the number of rows present for that campaign by 20k.

I can't use a partitioned view to do it because I need to log which underlying table the campaign was in for possible restore processes. As you suggest, a bit of dynamic SQL takes care of all that.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560929
Posted Thursday, August 28, 2008 7:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
Almost forgot... at my previous job, the archive process ran nightly. At my current job, it runs on Monday and Thursday nights. First run may take a day or more to complete, but it's online all the time. After that, the daily jobs (same as the first job) are a piece of cake and may only take a couple of seconds to execute depending, of course, how many rows slip past your cutoff date on any given day.

Some folks have suggested that such a thing be built into a trigger... past experience tells me that will eventually turn out to be a source of deadlocks on high volume systems.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #560931
Posted Thursday, August 28, 2008 10:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:03 AM
Points: 79, Visits: 211
thanks for your explaination. :D
Post #560989
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse