November 5, 2009 at 7:03 am
I have to delete 120 million records from a 2TB database which is taking 18 hrs to do so.
DELETE FROM EMP WHERE EMPID in ('23467896534','768945634','458920734','904798238','398476208')
Is there a way i can do more faster, may be in batches or any other method ?
November 5, 2009 at 7:12 am
not sure about faster but you certainly want to batch it up to avoid blowing your log
create table A_DBA_breakout (stopnow char(1)NOT NULL default ('N'))
insert A_DBA_breakout values('N')
create trigger breakout_insert on A_DBA_breakout
for insert
as
print 'insert to this table not allowed - restricted to one row'
rollback transaction
Declare @num int
update A_DBA_breakout set stopnow = 'N'
Set @num = 1 -- force it into loop first time (or could do count of no of records to be deleted )
While @num <> 0
Begin
begin transaction
set rowcount 10000 -- stop after 10000 rows, change to value you want
DELETE FROM EMP WHERE EMPID in ('23467896534','768945634','458920734','904798238','398476208')
set @num = @@rowcount -- capture no of rows deleted, when this is 0, will drop out of loop
commit transaction
--waitfor delay '00:00:01' --optional to let others in
if (select stopnow from A_DBA_breakout) = 'y'
begin
break
end
end
if @@trancount > 0
commit transaction
the breakout bit is so you can stop the query without having to roll anything back. you could set up a sqlagent job that just changes the value to stop the query at a set time.
edited - changed delete statement to your delete. BACKUP DB first!
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply