SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Queue Reader Agent error


Queue Reader Agent error

Author
Message
Dennis Post
Dennis Post
SSC Eights!
SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)

Group: General Forum Members
Points: 945 Visits: 547
Hi,
I've been searching a long time now to find out how to get rid of this error.

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.


This issue has been raised a few times here and lots of times else where, but no solutions.
http://www.sqlservercentral.com/Forums/Topic445184-291-1.aspx
http://www.sqlservercentral.com/Forums/Topic1168869-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1173906-291-1.aspx
http://www.sqlservercentral.com/Forums/Topic1038860-1550-1.aspx

Replication seems to be working fine. The big red circle with a cross in replication monitor makes us nervous though.

The publisher that shows the cross has 8 publications with a total of 9 subscriptions (1 each except for one with 2 subscriptions).
Windows 2003 (Clustered) SQL 2005 ==>> 2000 / 2005 / 2008.

I have been comparing dbo.syspublications, dbo.syssubscriptions, dbo.sysarticles to those on a similar box with similar replications. I can't find any differences in values that would point a possible configuration difference.

I have tried all sorts to try to resolve this.
USE master
exec sp_changedistributor_property
@property = N'heartbeat_interval',
@value = 5;


Bigger or smaller interval only changes the number in the error.

dibbydibby posted a query in one of the above links to see if the distribtor is overloaded.
-- Commands per article
select ag.name Publication, s.undelivcmdsindistdb, s.delivcmdsindistdb
from msdistribution_status s, msdistribution_agents ag, msarticles ar
where s.agent_id = ag.id and s.article_id = ar.article_id
order by undelivcmdsindistdb desc

-- Commands per Publication
select ag.name Publication, COUNT(*) Articles, SUM(s.undelivcmdsindistdb) undelivcmdsindistdb, SUM(s.delivcmdsindistdb) delivcmdsindistdb
from msdistribution_status s, msdistribution_agents ag, msarticles ar
where s.agent_id = ag.id and s.article_id = ar.article_id
GROUP BY ag.name
order by undelivcmdsindistdb desc


From this I gleamed that retrieving all 84 rows from msdistribution_status takes 15-30 secs. This is due to msrepl_commands having 7mils records. Is this a normal size?
The troubled publisher only ocassionally has 20+ under column undelivcmdsindistdb. If anything, it looks like another server is having problems with very high undelivcmdsindistdb values, but it's not.

I have also cleared out [Distribution].[dbo].msqreader_history then manually started job "Replication agents checkup". All agent_IDs are writing to this table.

Somewhere someone posted this query. Though, not what you should be looking for.
select 'dbcc inputbuffer(' + CAST(spid AS Varchar(5)) + ')' from sys.sysprocesses where program_name like '<Publisher>%'
dbcc inputbuffer(73)



Another suggestion was to run
EXEC sp_browsereplcmds


Which returns:
Msg 6513, Level 16, State 27, Procedure sp_browsereplcmds, Line 110
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.

I suppose this may or may not be the cause. Got 16 cores / 16 GB RAM on this clustedered box. 99.98% buffer hitting. No other reason to believe memory is an issue.

This just shows the unioned result msqreader_history tables between the troublesome publisher and the comparable publisher. Svr1 being the naughty one.
SvrName   agent_id   publication_id   runstatus   start_time   time   duration   comments   transaction_id   transaction_status   transactions_processed   commands_processed   delivery_rate   transaction_rate   subscriber   subscriberdb   error_id   timestamp
Svr1 1 NULL 1 2013-03-26 13:29:59.297 2013-03-26 13:29:59.297 0 Starting agent. NULL 0 0 0 0 0 NULL NULL 0 0x0000000005AFB7AE
Svr1 1 NULL 6 2013-03-26 13:29:59.297 2013-03-26 13:35:00.453 301 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. NULL 0 0 0 0 0 NULL NULL 0 0x0000000005AFBA7D
Svr2 1 NULL 4 2013-03-10 18:12:06.160 2013-03-26 17:16:44.530 1379078 No queued transaction available NULL 0 0 0 0 0 NULL NULL 0 0x0000000005A157F1



Could use some help on this one.
Cheers.

P.S. Sorry for the long post.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search