I have a Hit table, such as pretty much all Web sites, which contains all the hits to the Web site. For now, I have used a robot process to clean that table every 15 minutes for records older than two months. As the application grows, the number of hits are getting significant. As SQL Server does not allow to remove a record from a table as is, but to keep a log transaction on it, the process of deleting 200,000 records can be somewhat intensive. If there would not be any log transaction, that would be done pretty much instantly.

So, I need to look at a recycle approach to eliminate that robot process. The thing is, I need a recycle technique that will avoid collision. My main architecture is to assign a recycle record to a user which will not collapse with the same request from another user. I have 8 hits per second. How can I be sure to assign a record in the Hit table to be used for a recycle for one user which will not be the same as another user looking up the Hit table to detect which record to recycle which will not be the same as the other user?

When the application initializes, I am using a SyncLock approach from an object of oApp to make sure only one hit will initialize the worker process instance. So, I have four worker processes and if 10 hits are simultaneous, only one will initialize on a particular worker proces. Maybe I can use something similar here.