Re: transactional replication problem

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 09/23/04 

Date: Thu, 23 Sep 2004 18:04:17 -0400

If the distribution agen can't connect to the subscriber, and yet you can
connect to the subscriber manually, through QA or something similar, there
is a probelm with the distribution agent - probably resource depletion. I'd
try to reboot the subscriber or publisher to see if this clears the problem.

The blocking problem doesn't sound normal. I am wondering if perhaps it is
blocking itself while updating one of the system tables. Can you run sp_lock
to try to determine which table(s) it is locking on?

If you have a series of commands in a transaction, all of the commands will
be wrapped in a single transaction. If you have cascading deletes/updates
the lock will be held on both the parent and child table until both commands
complete and the entire transaction is committed.

Are these tables related in any way? 

-- 
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"OJ" <anonymous@discussions.microsoft.com> wrote in message
news:027a01c4a1af$f9d1c700$a501280a@phx.gbl...
> Hi,
> I have two-way transaction replication. Set of tables from
> several databases is being replicated from server A to
> server B. Problem is with only one database. Everything
> was working OK until today. Log Reader agent failed, and
> when I restarted log reader job of this database (always
> solved the problem before), distribution agent is in
> status Running (Last action: Connecting to Subscriber),
> and after a while it gets general network error.
> There is network connectivity (all other databases
> replicate OK, from both sides), so network is not a
> problem. Everything was working OK for several months, and
> whenever we had network issues between these two servers,
> restart of the jobs was enough.
> I also noticed something strange that might be causing
> this problem: In current activity, there is always one
> process (stored procedure for insert into one of the
> tables of that database, called by replication), that
> blocks another one (also stored procedure for insert into
> DIFFERENT table of the same database, called by
> replication). These 2 stored procedures/tables don't have
> anything with each other! Therefore, they shouldn't be
> blocking one another: one inserts into one table, and
> second one into different table!!!
> Both servers are sql server 2000, sp3... Network
> connection between is WAN.
> Thanks in advance,
> OJ

Relevant Pages

  • Re: SQL2000 - Merge Replication - Timeout error
    ... The timeout is in your publisher and distributor server, ... Then, update the database statistics. ... Run profiler on the subscriber to see what is going on? ...
    (microsoft.public.sqlserver.replication)
  • Re: Publishers disappeared from under replication monitor
    ... Basically the only database on the server being replicated suddenly had 'no' ... This seemed like some kind of corruption in the database. ... The next day we set up transactional replication again (Yes, ... > distribution agent name you can get a condition like this. ...
    (microsoft.public.sqlserver.replication)
  • Re: How to Replicate an SQL Server 2000 Database
    ... Looking for a SQL Server replication book? ... actual server name) enterprise manager should associate the database with ...
    (microsoft.public.sqlserver.replication)
  • Re: How to Replicate an SQL Server 2000 Database
    ... Looking for a SQL Server replication book? ... actual server name) enterprise manager should associate the database with ...
    (microsoft.public.sqlserver.replication)
  • Re: How to Replicate an SQL Server 2000 Database
    ... actual server name) enterprise manager should associate the database with the ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)