Post reply

trouble with snapshots in transactional replication

  • May 21, 2008 at 12:52 pm

    #122166

    we had transactional replication running fine for a couple of years, just most recently, when I snapshot a small article, it takes forever and sometimes I get

    '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.'

    Especially last night, I scheduled some snapshots to run once, figuring thats non-peak time and they should run fine. But they all failed and whats worse, which seems to me like they never seem to go away and blocked other active commands from going thru. I am not sure what was happening. Now I had to manually stopped all distribution agents and trying to figure out how to clear out the mess the snapshots left over. Could someone shed some light please?

    Thanks,

    Kathleen

  • May 21, 2008 at 5:13 pm

    #818924

    Kathy,

    I think its just due to heavy load on server when snapshot agent was executed. Just execute it once again and also look for CPU and memory counters while agent is executing.

    Manu

  • May 22, 2008 at 8:59 am

    #819193

    Thanks, Manu.

    I ended up disabling distribution sychronization on all other subscribers that dont need the snapshots. Then did the snapshots again and it worked.

    I guess the problem is as we are adding more and more publication and subscribers, replication cannot handle the load. Would it make sense to have a seperate server as the distributor? Right now, the publisher and distributor are on the same machine. Or is there a cost effective way to accomplish this?

    Thanks!

  • May 22, 2008 at 9:32 am

    #819209

    Go thru this link:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx#EYD

    Its pointing to "Transactional Replication Performance Tuning and Optimization" doc. Also, if load is high then its better to have separate distributor or you can have distributor on subscriber also.

  • May 22, 2008 at 9:36 am

    #819212

    I will look into it. Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply