Thanks for comment. I like the CTE coding but this seems to not include the small batch concept, without that end up with lock contention.

Agreed the count method consumes resources so initially against huge tables I hard code the count. Say the table has 50 million rows to remove, no point using count...

Thanks for the comment. Sorry these jobs runs on a schedule, initially against huge tables I run them every 20 minutes once the table is cleaned up I...

thanks for the comment. I also played with putting in a wait delay and that is another approach. What I do now is run these deletes as jobs...

This technique runs on our production systems for over 1 year - it reliably returns the oldest rows as long as the predicate is against the primary cluster index key....

Thanks Lana - somehow missed that in editing.:-)

This replication was on SQL 2005. Alas, my shop still has SQL 2000 despite my best efforts to upgrade. Highly recommend getting off SQL 2000.:-)

There are commands at the distributor which need to be cleared before replication resumes, I've found dropping the publisher the quickest method to clear them all out - but I...

Paul:

Thanks for the tip - I'll have to test out table diff and see good it is.

Hi Greg:

You'll find replication is quite exact. Setting the pre_cmd='none' only avoids the snapshot and prevent the overlaying of the subscriber data but it by itself is insufficient to...

Drop the article doesn't remove the bad commands at the distributor, as long as those commands are there replication won't begin. Dropping the publisher is the quickest method to...

This was a system I inherited, the articles were in alphabetical order so there were huge and small row count articles mixed together. I won't recommend such a grouping...

Yes step 4 is a manual resync since the delta rows missing on the subscriber are relatively few compared to the total rows in the article. I use Redgate's...

Yes pending replicaiton commands can be reviewed and rerun but it's a bit involved for a post reply. I will write about that in a following article. If...

I reran the process 10x as where in, inner join and exists. Overall the where in and inner join were statistically even with a slight edge to where in,...