Hi,

We have a peer-to-peer replication setup between four SQL 2008 nodes. While running profiler I noticed every 5 seconds (the polling interval of the distributor) a call is being made to update the transaction_timestamp and time columns in the MSreplication_subscriptions table. This call is being made every 5 seconds even if there have been no changes to any node.

exec sp_executesql N'update MSreplication_subscriptions set transaction_timestamp = cast(@P1 as binary(15)) + cast(substring(transaction_timestamp, 16, 1) as binary(1)), "time" = @P2 where UPPER(publisher) = UPPER(@P3) and publisher_db = @P4 and publication = @P5 and subscription_type = 0 and (substring(transaction_timestamp, 16, 1) = 0 or datalength(transaction_timestamp) < 16)',N'@P1 varbinary(14),@P2 datetime,@P3 nvarchar(10),@P4 nvarchar(4),@P5 nvarchar(4)',0x0000178700004419000100000000,'2015-01-28 20:20:39',N'PAR-EFS-01',N'EFS2',N'EFS2'

Does anyone have an idea why this occurs? As the timestamp value isn't changing when there has been no activity it seems unnecessary to make this call repeatedly. Since our replication is running over a WAN link with limited bandwidth it would be nice to minimize unnecessary traffic, or at least understand why it is occurring and necessary.

Another interesting observation is that this activity only occurs in one direction, from the last server to receive a transaction back to the publisher. That is to say, if servers A and B are in a peer-to-peer topology and A distributed the last transaction then B will repeatedly make this call back to A. 

Does anyone know the rationale behind this behavior?

Thanks.

Hi PrinceImrahil,

Do you get the error message?

,

Hi Malisaqin,

There is no error message - as far as I can tell the behavior is normal. I'm simply curious as to its purpose.

Hi PrinceImrahil,

Thank you for your question.

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Regards,
Michelle Li