December 10, 2009 at 4:40 am
I need to delete 5 million rows from a table without impacting any process that might be active during off hours. I though maybe I could run DELETE ... WHERE date between x and y, but fear I will still get lots of table locks, and it will be a pain to keep incrementing the date.
Would this be better (although slow), and run a block of records at a time (not sure what size yet). I could put it in a job, and run it over & over without having to change anything.
Is there a better idea ?
declare @primarykey-2 uniqueidentifier
declare @counter int
set @counter = 0
WHILE @counter <50000
begin
set @counter = @counter + 1
set @primarykey-2 = (select top 1 PKey from MyTable
where start <= '2006-10-31 23:59:59.999')
delete from MyTable
where PKey = @primarykey-2 AND start <= '2006-10-31 23:59:59.999'
end
December 10, 2009 at 6:05 am
There's a great article by Lynn Pettis that covers the topic very well.
I suggest that you take a look:
http://www.sqlservercentral.com/articles/T-SQL/67898/
-- Gianluca Sartori
December 10, 2009 at 6:14 am
Check also this post:
http://www.sqlservercentral.com/Forums/Topic466626-338-1.aspx
Have nice deleting with these two alternatives!
:hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply