Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2005 Replication - Distribution Agent - no commands Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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
Post #992144
Posted Thursday, September 23, 2010 11:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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


Post #992243
Posted Thursday, September 23, 2010 12:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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_id agent_id article UndelivCmdsInDistDB DelivCmdsInDistDB
280 1 BATCH 0 354
299 1 BOLAUDITLOG 0 3
318 1 BOLDLWEBUSERLOG 0 11
324 1 BOLLOGGEDINWEBUSERS 0 369
333 1 BOLUSERACTIONLIST 0 12
334 1 BOLWEBUSER 0 18

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.
Post #992259
Posted Thursday, September 23, 2010 12:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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?



Post #992269
Posted Thursday, September 23, 2010 12:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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
Post #992279
Posted Thursday, September 23, 2010 12:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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
Post #992281
Posted Thursday, September 23, 2010 12:56 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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...




Post #992294
Posted Thursday, September 23, 2010 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245

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.
Post #992300
Posted Thursday, September 23, 2010 1:48 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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.



Post #992324
Posted Sunday, September 26, 2010 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:27 AM
Points: 34, Visits: 245
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.
Post #993348
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse