Every 15 minutes, a recycle procedure occurs on a Hit table, hits from Web site, so to remove everything older than a specific amount of time. This used to work well so far. The operation only took about a few second to execution every 15 minutes. Thousands of records are removed everytime.

Since this afternoon, this operation started to create a deadlock on the Web site. So, we had to shut down the robot application. What I do not understand is why suddenly this is starting to create a deadlock on the Hit table making the Web site unable to respond anymore as every hit is logged in the Hit table. What I also do not understand is why this started now, where the number of hits is lower in this period of the year because of Christmas time. This used to execute with a high volume before and we never had a problem.

So, basically, a SQL select command is done to collect the primary key of one month ago. Then, a delete command follows to delete everything lower than that primary key.

This recycle has to continue to work. This is something that need to be done. Is it a flaw in SQL Server that this situation may happen just like that? Is there something that need to be done to avoid that? The other way would be longer but it would need to collect all the primary keys before and looping to delete each record one by one.

Any comment on how to better achieve that would be appreciated?