• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

SQL Deletion query loop

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'
0
Frias-JH
Asked:
Frias-JH
  • 3
  • 2
1 Solution
 
Ephraim WangoyaCommented:

try this


while exists(select count(1) from <table>)
begin
  set rowcount 1000
  delete from <table> where LogDate <=getdate()-1
  waitfor delay '00:00:01'
end

Open in new window

0
 
Ephraim WangoyaCommented:
modify to
while exists(select count(1) from <table> where LogDate <=getdate()-1)
begin
  set rowcount 1000
  delete from <table> where LogDate <=getdate()-1
  waitfor delay '00:00:01'
end

Open in new window

0
 
Frias-JHAuthor Commented:
That did it, I forgot the while statement :)

Any way to make it display a simple "1,000 rows deleted" per cycle?
0
 
Ephraim WangoyaCommented:

print '1,000 rows deleted'
0
 
Frias-JHAuthor Commented:
Thanks for the quick reply!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now