Removing large amount of data from a big table

Question

Removing large amount of data from a big table

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
November 5, 2008 at 11:05 am

#193247

Hi,
I have a question.I want to delete about 20,000,000 rows from a table which has 65,000,000 record.I have decided to do this task in SSIS package(Execute SQL Task).Also I have changed the Recovery model of Database to Simple.yesterday I run it,but it caused my Db went to recovery mode,today I have added some indexes to the tables, which are included in the join operation for populating data for Deletion.Does this make any problem for Db.I mean deleting huge amount of data from Database by using Deleet operation.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

dcordle SSC-Addicted Points: 469 More actions · Answer 1

how are you determining which rows to delete?

I would create a delete process using a 'while loop' or something similar, so that you end up deleting batches of records rather than the entire 20 million at once.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here is a code sample for dropping records from a table in batches:

declare @backupcnt int;

declare @backupfile varchar(128);

set @backupcnt = 0;

-- Delete records that are more than 5 years old from the current date.

while exists(select 1 from #ObjectOfDelete where EntryDate < dateadd(yy, -5, dateadd(dd, datediff(dd,0,getdate()),0)))

begin

delete top (10000) -- batch size is 10,000 records. This can be changed.

from #ObjectOfDelete

where EntryDate < dateadd(yy, -5, dateadd(dd, datediff(dd,0,getdate()),0));

set @backupcnt = @backupcnt + 1;

set @backupfile = 'd:\backups\dbname_tlog_' + cast(@backupcnt as varchar(8)) + '.trn';

backup log [dbname] to disk = @backupfile;

end

This method allows you to leave the database in the full recovery model.

Please note, that in this sample I have used a temporary table as the object of the delete. You would replace this temporary table with the name of your permanent table.

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 3

Thanks for the reply,

But when I try to run the query it said:

Msg 156, Level 15, State 1, Line 14

Incorrect syntax near the keyword 'top'.

It seems that I can not use Top (10000) after Delete command.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

What version of SQL Server are you running?

Also, post the SQL Code you used and got the error with, please.

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 5

I am using SQL SERVER 2000,Which it seems that it does not have "Top" feature in Delete and UpDate operation.

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 6

Aspet Golestanian Namagerdi (11/6/2008)
I am using SQL SERVER 2000,Which it seems that it does not have "Top" feature in Delete and UpDate operation.
Thanks

The title of this forum is T-SQL(SS2K5)

you should use the 2000 forum for your questions instead

* Noel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Aspet Golestanian Namagerdi (11/6/2008)
I am using SQL SERVER 2000,Which it seems that it does not have "Top" feature in Delete and UpDate operation.
Thanks

That explains it. You posted this in a SQL Server 2005 Forum, so I gave you a SQL Server 2005 solution.

Here is a SQL Server 2000 solution