Re: transactional replication problem
From: OJ (anonymous_at_discussions.microsoft.com)
Date: 09/24/04
- Next message: Lucy: "Odd error message."
- Previous message: RRN: "Re: Replication with Triggers"
- In reply to: Hilary Cotter: "Re: transactional replication problem"
- Next in thread: Hilary Cotter: "Re: transactional replication problem"
- Reply: Hilary Cotter: "Re: transactional replication problem"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Sep 2004 06:41:13 -0700
Hi,
I was able to find out what table was locked. Anyway,
server restarts were out of the question, so I killed that
process and replication started delivering transactions
plus transactions started being executed on subscriber.
All transaction from killed process were rolled back...
I'm wondering now, what happened with transactions
executed within process I killed (which was blocking
others)? Did they get deleted from distribution database
(on publisher)?
Thanks,
OJ
>-----Original Message-----
>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
>
>
>.
>
- Next message: Lucy: "Odd error message."
- Previous message: RRN: "Re: Replication with Triggers"
- In reply to: Hilary Cotter: "Re: transactional replication problem"
- Next in thread: Hilary Cotter: "Re: transactional replication problem"
- Reply: Hilary Cotter: "Re: transactional replication problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
