Bulk deletes in Transaction Replication causing latency

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

1. 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.
2. 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.
3. 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.
4. 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:

• Enhance Transactional Replication Performance
• Enhance General Replication Performance
• Delete an Article
• Delete a Publication
• Distribution Cleanup Job Failing Silently in SQL Server 2019 Transactional Replication Causing Distribution DB Growth to 1.5 TB - Microsoft Q&A
• How to fix transactional replication latency - Microsoft Q&A