Delete 29 million rows from 1 billion rows table. (SQL Server 2008)
I need suggestions from experts.
I need to delete 29 million rows from 1 billion rows table. This is SQL Server 2008.
I am sure foillowing query will work. I know that it will take an couple of hours or more to run it.
Does anybody have any suggestions/improvments in following queries? My main aim is to have these queries consume minimum resources and avoid 'transaction log full' error.
Declare @rows int
set @rows= 1
WHILE @rows > 0
begin
DELETE TOP (100000) FROM HugeTable
Where Key < 29660822
set @rows = @@ROWCOUNT
CHECKPOINT
end
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
jwarero
8/22/2022 - Mon
Guy Hengel [angelIII / a3]
it will only avoid that error if you put a WAITFOR DELAY into the loop, and ensure you have transaction log backups running during that "time" ...otherwise, you could also "delete at once ..."
Lara F
If you don't need maintain txn log for this DB
you can add this after update to avoid tempdb full error.
backup log databaseName with truncate_only