Bulk deletes in Transaction Replication causing latency

Question

Bulk deletes in Transaction Replication causing latency

Sowjanya Gopal 20

Hi.

I have around 10 publications on a database from Prod Server A to Reporting Server B. Created multiple publications so that any issue with one publication will not impact other publications.

There is a single huge article in one publication.

Due to space issues, we had deleted some few months old data in Subscriber server a few months back.

Deleted data was there in Publisher server till yesterday

Due to space issues, we had deleted this old data (~50GB) in Publisher server today in while loop in batches of 1000 records.

I know that this would cause issue in replication, since it would try to delete these records in Subscriber server as well.

I had set Distribution Agent profile for this publication to ignore Data Consistency errors so that it will ignore the Deletes.

As expected, it is skipping these rows. I expected it would skip rows quickly. But, since I had deleted millions of records & it is skipping rows in batch of 1000 records every few minutes, it is very very slow & latency is now 10 hours for this single article. It would take a few days to pass through this bulk delete command.

Please suggest solution to pass through this bulk deletes.

I am ok to remove this article from replication & remove this publication & subscription completely since it has only this single article.

But, I want to do it in safe way so as not to cause any other issues for other publications & Distribution DB.

Please suggest me safe approach.

To add to this, I found out only today that Distribution Agent missing & not running since 1.5 years and Distribution DB grew to 2.5TB.

I am planning to cleanup Distribution DB once this article issue is fixed.

Kindly suggest so any action would not messup the replication & Distribution DB

0 comments

2 answers

Your answer

Answer 1

You can drop the single-article publication and subscription. It's safe for the other 9 publications because transactional replication tracks commands per-publication.

Run on the Publisher, in the published database:

-- 1. Drop the subscription 
EXEC sp_dropsubscription @publication = 'YourProblemPublication', @article = 'all', @subscriber = 'ReportingServerB', @destination_db = 'SubscriberDB'; 
-- 2. Drop the publication EXEC sp_droppublication @publication = 'YourProblemPublication';

This removes that publication's Distribution Agent job and its undelivered commands from the distribution DB, scoped to only this publication.

What stays intact:

The shared Log Reader Agent keeps running for the other 9 publications.
Each other publication has its own Distribution Agent
The table on Subscriber B is not dropped; it remains as-is.

Avoid using sp_removedbreplication or drop all replication that removes your 9 good publications too.

On the 2.5TB distribution DB: do not purge the backlog in one operation , a large single cleanup transaction can overrun the distribution log and block all publications. Stage it with gradual retention reduction, and first confirm whether it was the cleanup job or the Distribution Agent that was actually missing, since that affects whether other subscribers are silently stale. Investigate on the servers before running any cleanup commands.

Answer 2

For a safe and faster way to get past this bulk delete and then remove the article/publication, focus on two areas:

Stop the pain from the current bulk delete / consistency errors
Since deletes on the Publisher are being replicated and then skipped at the Subscriber (because of the “continue on data consistency errors” profile), the Distribution Agent still has to read and attempt every delete, which is why latency is high. The safest way to stop this for a single article is:
- Remove the article from the publication using sp_droparticle on the publication database, with snapshot invalidation forced:
```
     USE <PublicationDatabase>;
     EXEC sp_droparticle
       @publication = N'<YourPublicationName>',
       @article     = N'<YourArticleName>',
       @force_invalidate_snapshot = 1;  -- required
```
- This removes the article from replication metadata but does not drop the table itself. The existing snapshot for that publication is invalidated, but other publications are unaffected.
- After this, new deletes on that table will no longer be replicated, so the Distribution Agent will not spend time processing those commands.
If the backlog already contains delete commands for this article, they will still be processed until the Distribution Agent reaches the point where the article is no longer part of the publication. To reduce the time spent processing commands overall, consider:
- Using stored procedure execution for future batch operations on large tables instead of row-by-row deletes, so replication sends one procedure call instead of millions of individual delete commands.
- Spreading articles across multiple publications (which you already do) and, if needed, using multiple independent agents so other publications are not impacted.
Safely remove the publication and clean up the distribution database
Once the article is removed and the backlog is under control, the publication and its replication metadata can be removed without affecting other publications:
- To delete the publication:
```
     USE <PublicationDatabase>;
     EXEC sp_droppublication
       @publication = N'<YourPublicationName>';
```
- This deletes only the publication definition and related replication objects for that publication. It does not drop user tables or other publications.
- If the publication database has no other publications and should no longer be a Publisher, disable replication for that database:
```
     EXEC sp_replicationdboption
       @dbname = N'<PublicationDatabase>',
       @optname = N'publish',
       @value = N'false';
```
- At the Subscriber, run sp_subscription_cleanup on the subscription database (for this publication) to remove remaining replication metadata for the dropped publication.
Distribution database cleanup after long downtime
Because the Distribution Agent was not running for a long time and the distribution database grew to multiple TB, ensure the standard cleanup mechanisms are working before any aggressive action:
- Verify the Distribution Clean Up: distribution job is enabled and running successfully. This job removes replicated transactions from the distribution database and deactivates subscriptions that have not synchronized within the maximum distribution retention period.
- Verify the Expired Subscription Clean Up job is enabled and completing successfully. This job removes expired subscriptions and allows associated metadata to be cleaned up.
- Check retention settings on the distribution database and publications. Very high retention values or never-expiring subscriptions cause the cleanup job to evaluate and retain a huge volume of metadata, which can make it appear to hang.
- Use Replication Monitor to identify subscriptions with large numbers of undistributed commands. For subscriptions that are far behind and where generating/applying a new snapshot is faster than processing the backlog, consider reinitializing them.
- Drop or allow expiration of obsolete subscriptions so their metadata can be removed by the cleanup jobs.
Performance tuning for future bulk operations
To avoid similar latency in future:
- Publish only required data and avoid unnecessary articles.
- Use stored procedure execution for batch updates/deletes on large tables.
- Use row filters judiciously; heavy filtering increases Log Reader and Distribution Agent work.
- Tune Distribution Agent parameters such as -SubscriptionStreams to apply changes in parallel when appropriate.

These steps allow safely removing the problematic article and publication, then letting the built-in cleanup jobs and retention settings gradually reduce the distribution database size without impacting other publications.

References: