msrepl_commands and msrepl_transaction table growing

Heisenberg 261 Reputation points
2022-04-14T22:43:09.573+00:00

hi folks,
our database tables MSrepl_commands and MSrepl_transactions are growing each day. currently _Commands table has around 21mil rows and _transaction has around 6million rows.
I also see distribution cleanup job recently has started taking 15-20 mins. I think thats unusual. When i see history of this job this job used to finish in seconds.

I also see all the publication properties setting has been set to "subscription never expire, but they can be deactivated until they are reinitialized".
Distribution property "transaction retention" has been set to 0-72, history retention set to "48 hours", transaction delete size is set to 5000, command delete batch size set to 2000.

I've also seen at a times in sp_whoisactive that distr. clean up job running command "delete TOP(@deletebatchsize_transactions) MSrepl_transactions" and it runs for 15-20 mins .

I have tracked execution of stored proc dbo.sp_MSdistribution_cleanup in our monitoring tool and it shows each iteration is at least deleting 100K rows.

Can someone help me fix this issue, currently there are no replication delays but it looks like this is a ticking timebomb.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
{count} votes