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

    Question

  • Hello,

     

       We are consistently getting the error message below on our subscribers that have blob images. Is there a way to increase a setting to avoid SQL to throw this error, or another suggestion? Thanks in advance.

     

    John

     

    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.

     

    18 -BcpBatchSize 100000
    18 -ChangesPerHistory 100
    18 -DestThreads 2
    18 -DownloadGenerationsPerBatch 5
    18 -DownloadReadChangesPerBatch 100
    18 -DownloadWriteChangesPerBatch 100
    18 -FastRowCount 1
    18 -HistoryVerboseLevel 3
    18 -KeepAliveMessageInterval 300
    18 -LoginTimeout 15
    18 -MaxBcpThreads 2
    18 -MaxDownloadChanges 0
    18 -MaxUploadChanges 0
    18 -MetadataRetentionCleanup 1
    18 -NumDeadlockRetries 5
    18 -PollingInterval 60
    18 -QueryTimeout 400
    18 -SrcThreads 2
    18 -StartQueueTimeout 0
    18 -UploadGenerationsPerBatch 3
    18 -UploadReadChangesPerBatch 100
    18 -UploadWriteChangesPerBatch 100
    18 -Validate 0
    18 -ValidateInterval 60

     

    Due to the memory leak issue with respect to replicating blobbed images we have changed UploadGenerationsPerBatch to = 3.

    Tuesday, March 27, 2007 2:23 PM

Answers

  • please refer to:

    http://msdn2.microsoft.com/en-us/library/ms146868.aspx

    especially the section:

    -- Change the heartbeat interval at the Distributor to 5 minutes.
    USE master
    exec sp_changedistributor_property
        @property = N'heartbeat_interval',
        @value = 5;
    GO

    Setting this to a higher value will have the agents only log at this higher value.

    This setting was introduced in SQL 7 IIRC.

    Friday, April 06, 2007 6:46 PM
    Moderator

All replies

  • Unfortunately, you are seeing something that I've had severe problems with at several customers.  Support refuses to acknowledge this as a problem and also refuses to offer any kind of valid solution.  My particular case was in having tens or hundreds of thousands of level 18 errors thrown into the logs for a normal operational state.  The only solution from support was to completely disable error logging for absolutely everything on the instance.  Yours is a slightly smaller issue, but still a major one.  Unfortunately, there is no setting at all to allow someone to suppress error messages.  My only solution was to run the SQL Server in an unsupported state by directly editing system stored procedures and commenting out the offending code within the replication procs.  I've filed several bugs on the tremendous amount of logging that the replication agents do, but every one has either fallen off the face of the earth or been closed as "by design".    (One of the customers that I shut the logging of 14151 errors off on saw their processor utilization on a 32 way machine drop from an average of about 75% to less than 25%.  So, I'm not really sure how a design feature of SQL Server could be to chew up about 50% of the processor cycles just to log error messages.)

     

    My first suggestion is to open a support case.  Maybe after a couple hundred people open support cases on the fact that logging in 2005 is substantial and a DBA has zero control over suppressing things that they consider a normal operational state, we might start getting somewhere.

    Tuesday, March 27, 2007 3:19 PM
    Moderator
  • Have you tried setting these 2 parameters:

    -OutputVerboseLevel 0

    -HistoryVerboseLevel 0

    ?

    Wednesday, March 28, 2007 4:35 AM
  •  

    Mahesh,

     

    I have used the above mentioned setting for transactional replication. Replication runs fine for 2 days or so and after that without any error message I get 'subscriber re-initialization' required message. I have replication running every minute for 12 publication and you would imagine the amount of logging from distribution agent. Unfortunately, running replication continously is not an option because we are currently experiencing network-related issue that drop the connection every 10 minutes or so.

     

    I would appreciate if you could offer me some thoughts on that.

    Tuesday, April 03, 2007 4:32 PM
  • please refer to:

    http://msdn2.microsoft.com/en-us/library/ms146868.aspx

    especially the section:

    -- Change the heartbeat interval at the Distributor to 5 minutes.
    USE master
    exec sp_changedistributor_property
        @property = N'heartbeat_interval',
        @value = 5;
    GO

    Setting this to a higher value will have the agents only log at this higher value.

    This setting was introduced in SQL 7 IIRC.

    Friday, April 06, 2007 6:46 PM
    Moderator
  • Hi,

    I am not sure if this will help anyone or not, but I had this same issue with my replicated environment.  After doing some digging into the system tables, I found that replication was simply in the middle of rebuilding indexes on some of my largest tables.  Below is that statement that I ran against the master DB and found that the "cmd" column was populated with "CREATE INDEX...".  I simply let it run and it eventually picked up again as usual.

     

    select spid, kpid, status, substring(program_name,1,50) as 'program_name', blocked, cpu, physical_io, hostname, cmd

    from master.dbo.sysprocesses

     

    Hope this help someone,

    Benny Blanco from the Bronx

    Sunday, June 08, 2008 11:59 PM
  • Hi Hilary,

    I setup PULL transaction repication on the following environment:

    SQL Server 2000 --> Publishers
    SQL Server 2008 SP1---> a remote distributor
    SQL Server 2008 SP1 ---> a subscriber

    The replication run into 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.

    I follow your suggestion by updating  the remote distributor

    use master;
    go

    USE master
    exec sp_changedistributor_property
        @property = N'heartbeat_interval',
        @value = 5;
    GO

    However, I still saw the error.   Could you please help and advise?

    Thanks a lot,
    Edwin


    None
    Thursday, December 03, 2009 11:55 PM
  • I BET YOU THAT  distributor clean up job is blocking the logreader which in return giving you the error. Check blocking on  Distributor server; Error message is misleading. Once you stop the clean up job you will see that replication resumes.  :)
    Tuesday, December 08, 2009 1:32 AM
  • In my experience this is rarely the case as the distribution cleanup task runs as its first statement is SET DEADLOCK_PRIORITY LOW which makes it the deadlock victim.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html 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
    Tuesday, December 08, 2009 2:11 PM
    Moderator
  • For me right now , deletion process is taking over 25 minutes each time it runs. towards to end insert statement is waiting in blocked  state.

    so basically distribution.dbo.sp_MSadd_replcmds is waiting to be inserted while EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

    does the deletes.

    We are highly transactional.   this is some what becoming a burden as  subscribers require close to up to date data....


    Wednesday, December 09, 2009 7:30 PM
  • Was there ever a solution to this problem found? We are in the process of migrating to SQL 2008 R2 Enterprise x64 and hare running into this problem. In my searches I've not yet found a definitive solution.

    Thanks all in advance

    Wednesday, September 15, 2010 2:52 PM
  • we are also highly transactional and are using SQL Server 2008 and our distribution cleanup job was taking forever.    I rebuilt key indexes inside the distribution database and our cleanup job went for 28 minutes to 4 seconds.
    Thursday, October 20, 2011 10:29 PM
  • I have this issue and tried that, it changed my message to "The replication agent has not logged a progress message in 5 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."

    Instead of 10 minutes, it's hard to tell if there's an actual problem or not.

    Friday, November 09, 2012 4:30 PM