I use QUEST Spotlight to monitor locking - this technique holds locks very briefly since it is only 2000 rows per transaction. Spotlight recycles every 6 seconds typically there...

Interesting idea I have not tried yet, worried about table outage and sp recompiles:-)

I size the TEMPDBs at 15GB each which is considerably larger than any single delete job can hit. In addition I keep 40B free on the TEMPDB disk drive...

Great question - actually this will impact replication. If you want the deletes to flow through to subscriber no issue other than some latency if not you can modify...

If I know there are 50 million rows to remove and I set @cnt = 1,000,000 then I can safely run the job 49 times and know that 1 million...

Great question. I run the child table cleanup jobs first then the parent job. Just to be safe I keep more days (16) in the parent table and...

I am a big fan of the 2 temp table approach and wrote about that on a previous article published at SQL Central. The difference here is to pull...

True enough if back dates are inserted. DBAs need to understand their data, in these tables the event_date is alway 'right now' upon insert so there is no worry...

BCP will work in some shops but mine is 24*7 so the deletes have to run online.

Thanks,

So much for my new proof reader:-)

Good point - I TLOG on the hour so haven't seen a disk space issue so far.

This will work if you want to delete all rows. The article keeps the last 6 days of data so you have to check how many rows left.

It doesn't need to as the delete uses the view and the view only returns 2000 rows. Since the @cnt limits the total number of rows to those with...

What level of lock contention do you see with this?

For those DBAs who inherit a 7*24 application and we can't change much in the database without business impact - this is a low impact method to cleanup tables. ...