The Multifunctioning DBA

Oct 30 2010   12:00PM GMT

Delete in Batch

Colin Smith Colin Smith Profile: Colin Smith

Today I needed to delete 28 Million Rows from a table and I did not want to do it as one large statement because that would kill my log.

Here is what I did.

–Set your rows to deal with
DECLARE @intloop int

Select @intloop = count(*) from Table
where Column < Something
While @intloop > 0

— how many to do in a batch
SET rowcount 500000

–do the delete
delete from Table  where Something < Something

set rowcount 0
–decrement intLoop
SET @intLoop = @intLoop – 500000

print ‘Batch Complete’
print @intLoop

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: