Batch Delete large amounts of data

-- Specify the number of rows you want -- to delete per 'gulp': declare @ count intset @ COUNT = 2000 -- keep track of the number of rows -- impacted by each gulp... once it -- drops below the intended number of rows -- then you're done... declare @ rowcount intset @ rowcount = @ count -- keep date same through entire operationdeclare @ cutoff datetimeset @ cutoff = getdate () -60 while @ COUNT = @ rowcount begin -- archiving logic goes here... -- (or it can take place before you -- start with this 'nibbling operation') -- remember, nibbling deletes aren't the -- only thing you can do, you can also do -- updates, inserts, merges, etc. delete from lorrylocations where locationid in (select top (@ count) locationid from lorrylocations with (nolock) Where [timestamp] <@ cutoff) -- Update the number of rows modified -- if it's less than @ count, then this was -- our last pass... and the while loop -- Will Break Set @ rowcount = @ rowcount -- a few milliseconds is typically all you'll -- need for most operations, but toggle this -- value as needed waitfor delay '000: 00: 00.400 'End