2.5TB Distribution DB. Distribution Cleanup Agent not running from 1.5 years

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

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.

2. 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.