SQL Server replication msRepl_commands msRepls_transactions tables growing

Question

I'm not getting any clue how to safely clean up the above tables. Last Friday, these tables had 22m and 6m records, respectively. Over the weekend, the number of rows has been reduced to 10m and 2.5m. Now in last two days these tables have been slowly growing. Current rows are 12m and 3.5m.

I'm trying to see if there are any old transactions using the following query, which gives me output -- 2022-04-16. Does it mean I do not have any transactions before this date, and all the rows in this table are the last three days rows?

SELECT (entry_time) 
FROM msrepl_transactions WITH (nolock)

The distribution cleanup job is running fine, with values of 0 and 72. History shows it's deleting small amount rows like 5,6. Max, I've seen it has deleted rows between 20 and 30. History is as follows. Am I interpreting this correctly?

"Deleted 6 row(s) per millisecond from MSrepl_commands [SQLSTATE 01000] (Message 22121) Deleted 1 row(s) per millisecond from MSrepl_transactions [SQLSTATE 01000] (Message 22121) Removed 9844 replicated transactions consisting of 27385 statements in 12800 milliseconds (2 rows/millisec). [SQLSTATE 01000] (Message 21010). The step succeeded."

In publication properties, the radio box is checked to "subscription never expires, but they can be deactivated..."

Can someone tell me how I can find out why this table has these many rows and is not getting cleaned up? Does this mean there was a large transaction run that might have caused this to fill up because min(entry_time) from _transaction table shows there is no older data before 04/16.

Answer 1

I cannot guarantee that these suggestions will solve your problem. These are common issues that I am aware of why data is not deleted from the two tables you mentioned even though the data has been replicated to all subscribers.

1. Please check that none of your publications has immediate_sync = true.
2. Please check that none of your publications has allow_anonymous = true.

Once you fix these two settings, you can run the cleanup job manually or reduce the max retention period.

In order to change the settings you can use this command as a sample.

DECLARE @publication AS sysname
SET @publication = N'AdvWorksProductTran' 

-- Turn off DDL replication for the transactional publication.
USE [AdventureWorks2012]
EXEC sp_changepublication 
  @publication = @publication, 
  @property = N'allow_anonymous', 
  @value = N'False'
GO

Reference:

1. Role of the Immediate Sync Option for SQL Server Replication by Mohammed Moinudheen
2. Impact of immediate sync
3. MSREPL_COMMANDS not cleaning out