none
The replication agent has not logged a progress message in 10 minutes

    Question

  • HI ALL,

    I was implemented Merge replication in sql server 2008, it was run fine few months, recently it was give error message like the below, if any one face this type of issue how to solve this, give me the solution, this was very urgent please help me in this.

    Error messages:

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

    Thanks, Prasad.

    • Moved by Tom Phillips Monday, March 14, 2011 8:47 PM Replication question (From:SQL Server Database Engine)
    Monday, March 14, 2011 8:45 AM

All replies

  • This is an indication that something is wrong:

    Verify that the subscriber can connect to the distributor

    Verify there is no blocking at the distributor and subscription databasebu using SSMS UI or any other means suchs as sp_locks: http://www.sql-server-performance.com/admin/article_images/sp_locks/sp_Locks.txt


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Monday, March 14, 2011 10:10 AM
  • Hi,

    Does replication eventually resume as normal or not?  Also, is this happening during snapshot generation and/or snapshot distribution?  I've seen this in my environments during snapshot distribution and I've come to believe this is somewhat normal with large snapshots and/or slow links.

    If replication eventually resumes as normal you could increase the distributor's heartbeat interval using sp_changedistributor_property to alleviate this error.

    Hope this helps.

    Tuesday, March 15, 2011 4:23 PM
    Moderator
  • check whether replication job is running or not
    Tuesday, March 15, 2011 4:54 PM
  • Hi Prasad,

    Do not Panic, solution is simple you need to check some points and good to go..

    1) Check the SPID working on this thread.

    2) Once you get the SQL its working on it will be obviusly the merge history table ie.. msmerge_contents or msmergemakegeneration table,

    3) check the publisher settings in generation level threshold in msmerge_publications at publisher DB, and it will default value will be 1000.

    4) Change it too "0" and stop the subscription running by killing the spic once you are conformed it was kiiled, re-run the subscription.

    Or some times it will automatically resumes form the point where it was working or blocked.

    5) once you are done change the setting back to 1000 value.

    The above fix is for when the caught SQL was working on msmerge_generations table....

    And there are chances your subscriber was working on Snapshot where it might take long time due to heavy data load, try tune the agent profile for the subscriber.

     

    Regards

    Jak

     

     

     

     

    Wednesday, March 16, 2011 2:50 AM
  •  Hi all,

    I go the above error but the data has been replicated from subscriber to publisher correctly. Why the message is displayed I don't know, please help me how to solve this error message.

    Thanks, Prasad.

    Thursday, March 17, 2011 10:07 AM
  •  Hi all,

    I go the above error but the data has been replicated from subscriber to publisher correctly. Why the message is displayed I don't know, please help me how to solve this error message.

    Thanks, Prasad.

    Like others mentioned already, It could be:

    1. snapshot is applying to the subscriber that taking longer than 10 minutes, as you already verified that your data did get across, you can think about to increase the check time interval to 20 minutes or 30 mintues, depends on your current profile.

    2. any blocking happened on the subscribers. You can set up jobs to periodically check any blocking of rep agent on the subscribers, throw alerts when blocking longer than your defined threshold.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    http://twitter.com/7Kn1ghts
    Thursday, March 17, 2011 11:41 AM
  • On your subscriber you need to do this

     

    select spid,blocked From sys.sysprocesses where program_name='Name of your merge replication job'

    armed with this spid do this;

    dbcc inputbuffer(spid)

    This will tell you the exact step the merge agent is working/paused/hung on.

    If the agent is blocked investigate the process which is blocking it.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, March 17, 2011 1:08 PM
    Moderator
  •  

    Hi Cotter,
    Thanks your valuable response, as per your advice, i was run the above query in my replication server it was give the below result.

    spid  blocked
    82     0
    83     0
    95     0
    96     0

    thanks, Prasad.

    Thursday, March 17, 2011 1:19 PM
  • OK, so what is the result of

    dbcc inputbuffer(82)
    dbcc inputbuffer(83)
    dbcc inputbuffer(95)
    dbcc inputbuffer(96)


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, March 17, 2011 1:46 PM
    Moderator
  • Hi Hilary,

     I have run the query in two server Publisher and subscriber also please find the below results.

    select

    spid,blocked From sys.sysprocesses  where program_name=

    'Name of the merge replication job''

    ---Subscriber

    spid  blocked
    269       0
    3856     0

    DBCC

    INPUTBUFFER(269

    )

    EventType 

    Parameters         EventInfo

    RPC

    Event  0                       sys.sp_MSmergeupdatelastsyncinfo;1

    DBCC

    INPUTBUFFER(3856

    )

    EventType 

    Parameters      EventInfo

    RPC

    Event  0                    sys.sp_MSenumchangesdirect;1

    --Publisher

    spid  blocked
    63     0
    64     0
    77     0

    DBCC INPUTBUFFER(63

    )

    EventType 

    Parameters   EventInfo

    RPC

    Event  0                sys.sp_MShelpmergearticles;1

    DBCC

    INPUTBUFFER(64

    )

    EventType  

    Parameters  EventInfo

    RPC

    Event    0               sys.sp_MSmergeupdatelastsyncinfo;1

    DBCC

    INPUTBUFFER(77

    )

    EventType

    Parameters   EventInfo

    RPC

    Event  0                 Database.dbo.MSmerge_ins_sp_8D44A18BCAE64F0A117EBE9FCAF74CC5;1

    Give me the solution, thanks inadvance, Prasad.

    Monday, March 21, 2011 11:53 AM
  • Its hard to say from this. Can you run this on your subscriber and see what it says? Has the problem cleared over the weekend?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, March 21, 2011 12:40 PM
    Moderator