September 23, 2010 at 9:48 am
Hi Guys,
i am having a problem with my Distribution agent. It is saying no replicated commands available while there are lots of commands waiting at the Distribution. It is a Transactional replication on SQL 2005.
The log reader agent is sending commands frequently to the Distributor but it is only the Distributing agent which is not pushing the commands.
Below are the commands that i ran on the Distributor and the result.
Below is extract of the results:
Log Reader
comments: 2 transaction(s) with 2 command(s) were delivered.
delivery_time:8655091
delivered_transactions:3332865
delivered_commands:10141021
average_commands:3
delivery_rate: 1171
delivery_latency: 0
Distributor agent
comments:No replicated transactions are available.
delivery_time:0
delivered_transactions:0
delivered_commands:0
average_commands:0
delivery_rate:0
delivery_latency:0
error_id:0
job_id:0x59BFF67CF73193449AD40A4D58556FAB
local_job:1
profile_id:4
agent_id:1
last_timestamp: 0x00000000002A6D19
September 23, 2010 at 11:46 am
ah - looks to be in the correct forum now...I posted a reply to this information on the other thread http://www.sqlservercentral.com/Forums/Topic212720-110-1.aspx
September 23, 2010 at 12:12 pm
Undistributed commands keep telling 0.
What is the result of this on the distribution DB:
SELECT SUM(rows) FROM sys.partitions WHERE object_id = OBJECT_ID('msrepl_commands');
Result: 4321764
I have ran your script as well and below is part of the results for 58 rows/artilce returned:
article_idagent_idarticleUndelivCmdsInDistDBDelivCmdsInDistDB
2801BATCH0354
2991BOLAUDITLOG03
3181BOLDLWEBUSERLOG011
3241BOLLOGGEDINWEBUSERS0369
3331BOLUSERACTIONLIST012
3341BOLWEBUSER018
I will have to spend some time reading the good article you sent. but in the meantime, if you can find something why ny subscriber is not getting the data will be great.
September 23, 2010 at 12:22 pm
From the script - are there any articles where the UndelivCmdsInDistDB is not 0 - the snippet you posted is for articles that have no (undelivered) data in the distribution DB.
And are you sure that the data hasn't been replicated? i.e. do you know of some data in the publisher that hasn't come in to the subscriber as yet to validate that the data hasn't reached the subscriber?
Can you check using tracer tokens (http://blogs.msdn.com/b/repltalk/archive/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens.aspx and http://blogs.msdn.com/b/repltalk/archive/2010/02/03/tsql-commands-to-generate-and-track-tracer-tokens.aspx) to see if the connection between the publisher to the distributor to the subscriber is fine?
September 23, 2010 at 12:36 pm
There is no undelivered commands for the distribution DB.
So last replication happened at 2am today morning and there are lots of data that have not been delivered to the Subscriber. However, the DIstrDB is still growing which means all the data are there.
I just stopped both agents.
Log reader is giving an error now: The process could not execute 'sp_MSadd_replcmds' on 'BSCDXDBR01\DXDBR'.
Distribution agent still no transactions.
The tracer tokens remain in pending on both agents. I have tried this a few times today but they remain pending
September 23, 2010 at 12:38 pm
I just saw this error too on the Log agent for not starting up:
Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'.Cannot insert duplicate key row in object 'dbo.MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'dbo.MSrepl_commands' with unique index 'ucMSrepl_commands'. (Source: MSSQLServer, Error number: 1007)
Get help: http://help/1007
September 23, 2010 at 12:56 pm
Well - for this error in the log reader agent "Cannot insert duplicate key row in object..." there's some some bad news - I've faced this error before and had to reset replication to get rid of this error...
To confirm if you're facing this error because of the same criteria that I faced:
a) Have you set the MaxCmdsInTrans switch for the log reader agent?
b) If you have set MaxCmdsInTrans - have you been receiving data in a single transaction that contains more number of commands than the value set for MaxCmdsInTrans?
c) Have you executed sp_startpublication_snapshot or sp_refreshsubscriptions?
If the above are true then you've encountered an edge case bug where the log reader agent seems to try to re-insert data into msrepl_commands and msrepl_transactions that already exists there.
In this case there is no option but to reset replication to get rid of this error - you could try to remove the offending transaction (using sp_repldone etc) but then there's no guarentee your data on the subscriber is in synch with the publisher...
September 23, 2010 at 1:02 pm
To confirm if you're facing this error because of the same criteria that I faced:
a) Have you set the MaxCmdsInTrans switch for the log reader agent?
b) If you have set MaxCmdsInTrans - have you been receiving data in a single transaction that contains more number of commands than the value set for MaxCmdsInTrans?
c) Have you executed sp_startpublication_snapshot or sp_refreshsubscriptions?
I haven't set MaxCmdsInTrans; but still how can i check if it is enabled?
Neither did i execute step c.
September 23, 2010 at 1:48 pm
To check if you have enabled MaxCmdsInTran run the following query on the distribution server:
-- check if the ss.command column for the step_name "Run agent." has
-- a "-MaxCmdsInTran" in the code
SELECT sj.job_id,sj.name,sc.name,ss.step_name,ss.command FROM
msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.syscategories sc ON
sj.category_id = sc.category_id
AND sc.name = 'REPL-LogReader'
INNER JOIN msdb.dbo.sysjobsteps ss ON
ss.job_id = sj.job_id
If you don't have this set then you're getting this error due to some other reason - and there might be ways to recover from this. I'm sadly unable to locate the scripts I used to identify the errant transaction and to skip over it and continue replication - it was based off what was provided here http://blogs.msdn.com/b/chrissk/archive/2009/09/08/how-to-skip-a-transaction-in-sql-2005-2008-transactional-replication.aspx
The steps are:
a) Identify the transaction that is trying to insert an existing xact_seqno,publisher_database_id record into the msrepl_transactions table - and the transaction that is trying to insert an existing xact_seqno,command_id,publisher_database_id record into the msrepl_commands table
b) Dummy update the xact_seqno to ensure that log reader agent doesn't pick them up (this is the vague part that I had in the script that I cannot locate)
c) skip this transaction and continue processing other data
d) try to manually synch the data in the transaction that was skipped
This could lead to data inconsistencies in the subscriber and other consistency errors down the line (for e.g. an insert was skipped then a future update of that record will fail).
If it is feasible I'd recommend setting up replication again - how long does that take in your environment? The drawback with setting up replication again is that you'll not know why this error happened and if it happens again it'll be back to square 1.
September 26, 2010 at 3:39 am
Winash,
Thanks a lot for the information. We have finally decided to rebuild the replication; we believe that is the safest and easiest way as a solution. But we have learnt a lot from the troubleshooting hints you gave.
Thanks.
June 29, 2012 at 11:50 pm
Hi,
I also have the same error "No replicated transactions are available" although there are many transaction on distributor to be applied. I have got no error in any of the replication jobs.
I am using transactional replication on SQL server 2008. When I run sp_MSenum_distribution, then status against distribution agent is coming 4 while when it is running fine status would have been 3 as in case of one other distribution agent using same distributor but different publisher db and subscriber db. The subscriber is configured for pull subscritption
Thanks
Vivek
July 3, 2012 at 11:57 pm
pls help on above.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply