Best way to DELETE and archive the data

Question

Best way to DELETE and archive the data

Nagaram

SSCrazy

Points: 2110
More actions
October 21, 2013 at 11:25 am

#281600

Hi Experts,
Looking for best way to archive and delete the exsting data from one table in the business hours.
We have one log table , An application is inserting the more than 1 lac records within an hour due to this size of log table is
becoming huge, we wanted to archive the log table data and truncate the existing data for every 4 hours ..

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Nagaram (10/21/2013)
Hi Experts,
Looking for best way to archive and delete the exsting data from one table in the business hours.
We have one log table , An application is inserting the more than 1 lac records within an hour due to this size of log table is
becoming huge, we wanted to archive the log table data and truncate the existing data for every hours ..

Probably the most direct way is to use OUTPUT.

http://technet.microsoft.com/en-us/library/ms177564.aspx

Something like this:

delete SomeTable

output deleted.[Column1], deleted.[Column2]

into YourLogTable

where SomeCondition

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Nagaram SSCrazy Points: 2110 More actions · Answer 2

No where SomeCondition..

need to delete all the records without logging the individual row deletions in tranasaction log

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Nagaram (10/21/2013)
No where SomeCondition..
need to delete all the records without logging the individual row deletions in tranasaction log

Easy enough to remove the condition.

You can't avoid row deletes being logged. That is part of Atomicity. If you are seeing lots of locking you may have to do your deletes in batches to help minimize locking.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jonysuise Ten Centuries Points: 1206 More actions · Answer 4

Hi SSC Journeyman, 😀

Maybe another approach could be partitionning your table.

Hope it helps. 😎

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

jonysuise (10/21/2013)
Hi SSC Journeyman, 😀
Maybe another approach could be partitionning your table.
Hope it helps. 😎

+1. You beat me to the punch on that one. I'm going through that right now.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)