MSrepl_commands table is increasing continuously.

Question

Post reply

MSrepl_commands table is increasing continuously.

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

eric.lyons SSC Eights! Points: 860 More actions · Answer 1

I had this problem a year ago and was given 2 options:

1) drop the replication and recreate from scratch.

2) set up a purge job in the system distribution db.

I chose 2.

This purge job has been working fine for a year.

delete from msrepl_commands

where xact_Seqno in (

select distinct xact_seqno

from MSrepl_transactions

where entry_time < getdate() -5 )

;

delete from MSrepl_transactions

where entry_time < getdate() -5;

*****

Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P

M&M SSC-Insane Points: 21700 More actions · Answer 2

eric.lyons (4/20/2012)
I had this problem a year ago and was given 2 options:
1) drop the replication and recreate from scratch.
2) set up a purge job in the system distribution db.
I chose 2.
This purge job has been working fine for a year.
delete from msrepl_commands
where xact_Seqno in (
select distinct xact_seqno
from MSrepl_transactions
where entry_time < getdate() -5 )
;
delete from MSrepl_transactions
where entry_time < getdate() -5;

Eric,

Thanks for sharing your experience. Just wanted to find out if you tried using the procedure from the distribution clean up job:sp_MSdistribution_cleanup to resolve your issue.

This procedure internally calls few other system procs and purges the data based on certain conditions. Quite not sure if step (2) described above meets those conditions. Due to this we may not be entirely sure about the implications on your replication setup. However, no issues until you face issues in your replication system 🙂

M&M

eric.lyons SSC Eights! Points: 860 More actions · Answer 3

In response to your questions: I believe I did. It was a year ago, however, so I can't be sure.

I went through extensive process to try to resolve the issue.

*****

Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

From performance perspective, you can use the concept of remote distributor and create the DB which multiple files plus use separate disk.this will improve the performance as it seems that your server is playing with heavy number of transactions.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Eric Zierdt Ten Centuries Points: 1333 More actions · Answer 5

I could start a new post for this, but since a lot of work has been done in this thread, I think I'm going to reply to this thread. I've got a SQL 2008R2 Enterprise server, with SP2 that is exhibiting the same symptoms. I have Immediate_Sync set to 0, my min threashold is 0, my max is 60 (was 96, I've been lowering it). My clean up job runs every 10 minutes and almost always finds 0 records to delete. I've been watching my MSRepl_Commands table grow by about 10 million records a day.

I'm not sure what else to look at, at this point. I looked at MSdistribution_status and I can see UndelivCmdsInDistDB drop down to 0 records when replication runs and the DelivCmdsInDistDB grows. I've tried manually running EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 60 and it comes back with "Removed 0 replicated transactions consisting of 0 statements in 0 seconds"

I tried running the cleanup command with a lower max threshold but it also, didn't delete any records.

I am not keen on the idea of manually deleting records. Just wondering if anyone has any other ideas for things I could look at?

Thanks.

Eric Zierdt Ten Centuries Points: 1333 More actions · Answer 6

Eric Zierdt (10/9/2014)
I could start a new post for this, but since a lot of work has been done in this thread, I think I'm going to reply to this thread. I've got a SQL 2008R2 Enterprise server, with SP2 that is exhibiting the same symptoms. I have Immediate_Sync set to 0, my min threshold is 0, my max is 60 (was 96, I've been lowering it). My clean up job runs every 10 minutes and almost always finds 0 records to delete. I've been watching my MSRepl_Commands table grow by about 10 million records a day.
...

I searched my agent job messages for the results of the clean up job, it has records going back to Oct. 2nd and none of the records indicate any records being deleted.

SELECT * FROM dbo.sysjobhistory WHERE job_id = <cleanup job id> AND step_id = 1 AND message NOT LIKE '%Removed 0%'

If I remove the Message NOT LIKE condition and just view all results, I can verify that all of them show "Removed 0 replicated transactions consisting of 0 statements"

Eric Zierdt Ten Centuries Points: 1333 More actions · Answer 7

Digging into this more, I see that the exec dbo.sp_MSmaximum_cleanup_seqno call isn't returning a @max_cleanup_xact_seqno record.

This means the query

select top 1 @max_cleanup_xact_seqno = xact_seqno

from MSrepl_transactions with (nolock)

where

publisher_database_id = @publisher_database_id and

(xact_seqno < @min_xact_seqno

or @min_xact_seqno IS NULL) and

entry_time <= @min_cutoff_time

order by xact_seqno desc

returns nothing. I debugged it and it's doing a loop that populates the @min_xact_seqno variable with various values. Most would return a @max_cleanup_xact_seqno, but the last value placed in @min_xact_seqno isn't found at all in the msrepl_transactions table. So I'm wondering if this indicates an issue? Anyone know?