Bulk deletes in Transaction Replication causing latency

Sowjanya Gopal 100 Reputation points
2026-07-01T17:16:50.05+00:00

 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

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments
Answer accepted by question author
Deepesh Dhake 1,165 Reputation points
2026-07-06T15:35:05.5566667+00:00

Transactions are per-publisher-database. One publisher transaction touching multiple articles produces one MSrepl_transactions row plus multiple MSrepl_commands rows tagged by article. The cleanup low-water mark advances per publisher_database_id, and sp_MSdistribution_cleanup only purges a transaction when all its commands are eligible across all subscriptions for that database. So if any article or subscription on database 9 still references a range of transactions, the whole range for database 9 stays put.

Two separate problems in your data:

  1. The dropped article's publication (database 9, 17-Feb-2025 onward). You dropped the article, but the publication (now 0 articles) and its subscription still exist. That subscription is still a registered destination cleanup accounts for. Dropping the subscription then the empty publication should release these transactions.
-- On Publisher, in the published database
EXEC sp_dropsubscription 
    @publication = 'YourPublication',
    @article = 'all',
    @subscriber = 'ReportingServerB',
    @destination_db = 'SubscriberDB';
EXEC sp_droppublication 
    @publication = 'YourPublication';

Then run one cleanup pass and check whether MIN(entry_time) for publisher_database_id = 9 moves forward. It should, provided no other article on database 9 still pins those same xact_seqnos. Confirm push vs pull first: if pull, also run sp_droppullsubscription on the subscriber.

  1. The Test DB rows (Dec-2024, publisher_database_id 1, 2, 4, 7, 8). You dropped those publications and databases long ago, yet the transactions persist. That's orphaned distribution metadata from an incomplete removal, cleanup still thinks something needs them, so it never purges them.

The dropped article's entries persist too only if its removal is incomplete. If you drop the subscription and empty publication cleanly, the transactions become eligible and get cleaned. The Test DBs prove that incomplete removal leaves permanent orphans. The difference is entirely whether the metadata is fully gone afterward.

Diagnose the orphans (run in distribution DB):

USE distribution;
GO
SELECT publisher_database_id, publication_id, article_id, subscriber_id
FROM dbo.MSsubscriptions ORDER BY publisher_database_id;
SELECT id, publisher_database_id, publication, subscriber_db, subscription_type
FROM dbo.MSdistribution_agents ORDER BY publisher_database_id;

Check whether ids 1, 2, 4, 7, 8 still appear. If they do, those leftover rows are what pin the Dec-2024 low-water mark.

Was this answer helpful?

2 people found this answer helpful.
0 comments
Answer accepted by question author
Deepesh Dhake 1,165 Reputation points
2026-07-02T15:53:15.09+00:00

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.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-07-01T17:17:16.3733333+00:00

    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:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Deepesh Dhake 1,165 Reputation points
    2026-07-05T02:41:53.9033333+00:00

    sp_MSdistribution_cleanup deletes based on delivery status combined with retention, not age alone. It won't purge transactions that are still undelivered or still referenced by a subscription, no matter how low you set max_distretention. So the rows being deleted are newer, delivered ones. The oldest transactions are stuck because something still needs them or points to them. Lowering retention further won't move the low-water mark if the blocker is delivery/reference-based rather than age-based.

    You can try running these queries:

    USE distribution

    GO

    -- Oldest transactions still present

    SELECT MIN(entry_time), MAX(entry_time), COUNT(*)

    FROM MSrepl_transactions;

    -- Per-agent delivery progress — find agents that are dead or lagging

    SELECT da.id, da.publisher_db, da.publication, da.subscriber_db,

    MAX(dh.time) AS last_delivery

    FROM MSdistribution_agents da

    LEFT JOIN MSdistribution_history dh ON dh.agent_id = da.id

    GROUP BY da.id, da.publisher_db, da.publication, da.subscriber_db

    ORDER BY last_delivery;

    -- Orphaned agents whose publication no longer exists

    SELECT * FROM MSdistribution_a gents da

    WHERE NOT EXISTS (

    SELECT 1 FROM MSpublications p WHERE p.publication = da.publication

    );

    GO

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.