Monday, March 22, 2010

Deleting millions of rows from SQL

Occasionally you can end up with very large databases that you need to purge old data from. Unfortunatly you cant delete all the old data in 1 chunk since it will cause you SQL server to stop serviceing anything else (and potentially fill up your logs). Below is a little script I used to delete small chunks of data quickly and efficiently.


while 1 = 1
begin

delete top ( 1000 )
from event_33
where TimeGenerated< getdate()-90

if @@rowcount = 0 BREAK

end

No comments: