ReplTip – Distribution cleanup Job running but shows zero rows deleted

Share this Post

 

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.


Share this Post

One thought on “ReplTip – Distribution cleanup Job running but shows zero rows deleted”

  1. Hi,

    But in my case the number of rows in msrepl_commands are not reducing and cleanup job says 0 rows deleted. How can we fix this?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.