ReplTip – Distribution cleanup Job running but shows zero rows deleted

By Taiyeb Zakir, Microsoft SQL Escalation Services

Consider the following scenario: Distribution cleanup Job is running and you see that number of rows in msrepl_commands and msrepl_transactions are reducing but the Cleanup Job history says 0 rows deleted:

For example:

Removed 0 replicated transactions consisting of 0 statements in 12961 seconds (0 rows/sec). [SQLSTATE 01000] (Message 21010).  The step succeeded.

This is because of the logic in our cleanup stored proc sp_MSdistribution_delete. This calls sp_MSdelete_publisherdb_trans for each Published database but we don’t aggregate variables @num_transactions and @num_commands. It stores values for the last published database for which cleanup was done. So even if millions of rows are deleted for other Published databases and there are no rows deleted from the last published database, it will say zero rows deleted.

We’re exploring ways to improve the reporting.