We have a database which uses SQL 2008. This application generates about 50,000 log files per day (each a row on the table) and I just found out that it hasn't been cleaned out in months. I want to create a query which will delete rows in the table in groups of 1000, then pause for a moment (to allow other proccesses CPU time, I don't want to choke the server) and then begin again. I have the basic deletion part but I can't seem to figure out how to make it loop. Here is what I have
set rowcount 1000
delete from <table> where LogDate <=getdate()-1
waitfor delay '00:00:01'