The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity

Peter Parker 0 Reputation points
2023-05-30T04:38:15.3933333+00:00

I'm introducing a new node for my Peer-to-Peer SQL subscription. Everything looks good, but when I look at the data, I found that it is not replicated correctly. I discovered this issue in the Replication Monitor, specifically in the Distributor To Subscriber History tab.

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Please verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

I increased the heartbeat to 20, then 30, 60, or 600 minutes and waited for 10 hours, but the issue still persists. The Replication Monitor shows that it is stuck at Initializing.

I searched for internet tutorials and enabled Verbose Logging to find out what happened. Upon checking the log, I noticed that it is stuck at 'Agent waiting for Peer-to-Peer commands to apply':

2023-05-30 01:56:12.471 Initializing 2023-05-30 01:56:12.737 OLE DB Subscriber 'SERVER-CANADA': exec sp_MSset_subscription_properties @publisher = N'SERVER-US', @publisher_db = N'mywebsite', @publication = N'mywebsite', @subscription_type = 0, @allow_subscription_copy = 0, @queue_id = N'', @update_mode = 0, @attach_version = 0x76849c648b374d4da379c7eae3b7f260, @queue_server = N'' 2023-05-30 01:56:13.018 OLE DB Distributor 'SERVER-US': exec sp_MSreset_subscription_seqno 24, 1 2023-05-30 01:56:13.018 OLE DB Subscriber 'SERVER-CANADA': exec dbo.sp_MSupdatelastsyncinfo N'SERVER-US', N'mywebsite', N'mywebsite', 0, 1, N'Synchronization in progress' 2023-05-30 01:56:13.815 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:19.081 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:24.347 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:29.613 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:34.879 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:40.154 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:45.420 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:50.686 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:56:55.952 Agent waiting for Peer-to-Peer commands to apply. 2023-05-30 01:57:01.234 Agent waiting for Peer-to-Peer commands to apply. (.... Repeating logs: Agent waiting for Peer-to-Peer commands to apply. ....)

To investigate further, I checked for pending commands :

declare @publisher sysname
declare @publicationDB sysname
declare @publication sysname
set @publisher='...'
set @publicationDB='...'
set @publication='...'
select transaction_timestamp, *
From MSreplication_subscriptions
where publisher=@publisher and publisher_db=@publicationDB and publication=@publication
transaction_timestamp : 0x00000000000000000000000000000000

Then I using this query to list pending commands for replication:

USE distribution;
 SELECT TOP 100 xx.Xact_seqno, COUNT() AS Xcount FROM dbo.msrepl_commands xx WITH (nolock) WHERE xx.publisher_database_id = 1 AND xx.Xact_seqno > 0x00000000000000000000000000000000 GROUP BY xx.Xact_seqno
 --HAVING count() > 100000
ORDER BY xx.xact_seqno

I selected the first result and ran 'sp_browsereplcmds' to check the command. What I found were two lines of commands:

-- INITIALIZE WITH BACKUP -- INITIALIZE WITH BACKUP

At this point, I'm unsure about the next steps. Could someone please advise me on what to do? Thank you very much. The version of SQL Server I'm using is 2014.

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,312 questions
0 comments No comments
{count} votes