Sunday, February 19, 2012

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


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.


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.

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.


Have you tried setting these 2 parameters:

-OutputVerboseLevel 0

-HistoryVerboseLevel 0




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.

|||please refer to:

especially the section:

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

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

This setting was introduced in SQL 7 IIRC.

No comments:

Post a Comment