How to Delete Large Amounts of Data

Question

How to Delete Large Amounts of Data

Viewing 3 posts - 31 through 32 (of 32 total)

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

Another suggestion is: don't the table accumulate 10s or 100s of millions of rows. Use a job to truncate off-hours on a daily or weekly schedule.

For example, on some servers, we have one generic data purge job and a configuration table containing: table name, retention days, and name of the date column.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

aroggeband-815867 wrote:

We had the same problem today.
I wrote a script that does the following:
Starts a transaction
retrieves the last id number of the table
uses sp_rename to rename the PK, indexes and constraints on the table
sp_renames the table
executes the create table script for the table to create a new, empty log table
reseed the Log_ID to one more than the value retrieved before renaming
end the transaction (rollback for testing, commit when it runs clean)
The script cleaned up 36 million records in a fraction of a second.

If I am understanding correctly , would this not just be a drop table and recreate the table (so that it has no rows) and with a starting seed value of the other table's max seed value + 1?

----------------------------------------------------

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

jonathan.ellison wrote:

This is a very relevant article and far too often code is written in such a way that Log space is consumed as a result of attempting to delete too much data in one action. Knowing the "sweet spot" in how many rows of data can be easily deleted quickly is crucial. This will differ based on the construction of the table: number of columns, data types, amount of data per row, etc...
Here is a simple code I use for handling such deletes.
Declare @CT INT -- variable used to compute number of records to be deleted
Set @CT = (select count(ID) from table where CreateDate < GetDate()-60)
While @CT > 0
Begin
Delete top 4000 from Table where CreateDate < Getdate() -60
Set @CT = @CT -4000
End
In this scenario, 4000 is the optimal rows of data to be deleted in a single pass. This simple loop will quickly and easily cycle through the data to be deleted without impacting the LOG file. Each cycle is fully committed so in case of a ROLLBACK, only 4000 records would need to be recovered via ROLLBACK.
Yes, I fully understand there are other ways to code this, and that is what makes SQL such a great tool. The goal is to produce the end result in an efficient manner with minimal impact. I hope this simple tip helps you become a better at your job.

You could incorporate a log backup command after about 10, 50, 100 of these passes to keep the log from having to auto grow. Also I would add a "WAITfor Delay" command to allow log and other connections time to conduct their operations and reduce the number of blocks.

----------------------------------------------------