Problem with distribution agent

Can anyone help me on this?

I have a transactional replication going on from Server A to server B (Sql Server 2005)

The replication was working well till yesterday..

But now, the replicated transactions are not getting applied at the subscriber. When I see the Log reader agent, its working fine...delivering the transactions to the distributor. When I see the replication monitor status ..it says 'delivering replicated transactions'...however the number of undistriuted transactions is very high..its getting bigger and bigger..not decreasing...I dont see any errors..So my guess is the distribution agent is not working eventhough its says 'delivering replicated transactions'..Other thing is if i see the replication monitor it says status excellent...and latency as 00 ..but i dont see the latest data at the subscriber..the subscriber is way behind the publisher..

can anyone help me on this? how do i resolve this problem? I stopped the distribution agent and again started it..but it dint help...what should i do?

thanks

Are any changes being applied at all? Or did you just complete some very large batch process, like an update of 1 million records inside a single transaction?|||

Thanks for ur reply..

I dont think the changes are being applied...in last 20 -24 hrs..the data at the subscriber is same...i dont see new records at the subscriber in last 20-24 hrs..undistributed transactions count keep on increasing..(now its 22074840 ...time needed to apply these transactions keeps on increasing...now its (18 hrs )..

I am not sure whether we updated large records in a single transaction...but even if we had updated...subscriber should catch up with the publisher in 20-24 hrs ..Does it take so long to apply those changes at the subscriber?

what should i do to resolve this issue? how do i force those undistributed transactions to be applied at the subscriber?

log reader is working fine...replication status is excellent.latency is 00...only thing is the replicated transactions are not getting applied at the subscriber..the distribution database is at the subscriber (i.e. subscriber is also distibutor)...

Could you plzz help me to solve this problem

Thanks

|||

Hard to say what the problem is, have you done any investigation at hte subsriber, is anything blocking the distribution agent? You can also stop the distribution agent and run it from the command line with an increased OutputVerboseLevel, it might give you some more information.

Otherwise I'm not really sure what the issue is without more information. You can try profiling the subscriber to see if the distribution agent is even connected.

|||

Hi, vjai!

I have almost the same problem. Did you look at MSdistribution_history on distributor?

select * from MSdistribution_history
order by [time] desc

|||

I am having the same problem. The only thing that fixed it was re-starting the SQL Service and then re-starting the distribution agent. This is not a solution that we can do during business hours though. Does any body have any other suggestions on how to resolve this?

thanks,

Amir

|||I agree it's not a good workaround, however without identifying the problem it's hard for us to offer a proper solution.|||

From my experience, this kind of problem occurs whenever we do massive updates in a single batch and also if there are triggers on the subscriber. In my case, i had a update trigger at the subscriber and whenever i do large number of updates on that table (on publisher) in a single batch , I get this problem.

|||OK, so it's not a distribution agent problem then? Just like at the publisher, the transaction isn't committed until the end, so same goes at the subscriber. If the trigger is causing performance issues - it's probably firing once for every update - your transaction size is probably growing very large. You can work around this by committing smaller transactions at the publisher, or investigate the subscriber to see if there's something in the trigger that can be optimized, or maybe it's a hardware issue.