Replication failed when destination tabel is using by runnable pro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ericsson - 22 Dec 2006 03:53 GMT

hi all,

my sql server has sp4 installed. i have two databases, D1 and D2. And the
transaction replication copys table D1.T1 to D2.T1 at 1:00pm everyday.

the problem is:
when some applications are continuously using table D2.T1, i mean those
processes are in runnable status. And then the replication starts and try to
copy data from D1.T1 to D2.T1, it will fail.

is there a way to work around this without having to kill those running
process which are using table D2.T1 at that time?

Reply to this Message

ericsson - 22 Dec 2006 04:07 GMT

btw, the server is running as a publisher, distributor and subscriber.
Database D1 and D2 are hosted on same server. The subscription type is a pull
subscription.

> hi all,
>
[quoted text clipped - 8 lines]
> is there a way to work around this without having to kill those running
> process which are using table D2.T1 at that time?

Reply to this Message

Hilary Cotter - 24 Dec 2006 02:51 GMT

You have two options. You can run the distribution agent continually to
minimize the impact occurring at 1:00 pm, or you can schedule it to stop
around 12:50 pm and restart when the other processes are complete.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> hi all,
>
[quoted text clipped - 9 lines]
> is there a way to work around this without having to kill those running
> process which are using table D2.T1 at that time?

Reply to this Message

ericsson - 24 Dec 2006 10:18 GMT

Hi Hilary,

Thank you for your information. i understand your words as: deliver
transactions only when no other processes are using table D2.T1, or using
continually transaction delivery, right?

Actually, it had been working perfectly with same configuration before i
upgraded the server from sp3 to sp4. Did anything related to replication be
changed in sp4?

> You have two options. You can run the distribution agent continually to
> minimize the impact occurring at 1:00 pm, or you can schedule it to stop
[quoted text clipped - 13 lines]
> > is there a way to work around this without having to kill those running
> > process which are using table D2.T1 at that time?

Reply to this Message

Hilary Cotter - 24 Dec 2006 12:06 GMT

There were bug fixes, but none which has caused (AFAIK) this problem on
other systems. Perhaps you have another process running on your system at
1:00 am which is causing extra load on your system and the overall
degradation is causing this problem to occur.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi Hilary,
>
[quoted text clipped - 26 lines]
>> > is there a way to work around this without having to kill those running
>> > process which are using table D2.T1 at that time?

Reply to this Message

ericsson - 24 Dec 2006 12:41 GMT

Maybe you are right. I'll check it. Anyway, i'd like to know if transactions
can be delivered to a table which is BEING USED by applications simultaneity.

> There were bug fixes, but none which has caused (AFAIK) this problem on
> other systems. Perhaps you have another process running on your system at
[quoted text clipped - 31 lines]
> >> > is there a way to work around this without having to kill those running
> >> > process which are using table D2.T1 at that time?

Reply to this Message

Paul Ibison - 27 Dec 2006 19:50 GMT

It is usually inevitable that there will be some blocking issues in this
circumstance. You can mitigate these by using NOLOCK or upgrading to SQL
Server 2005 and using the read committed snapshot isolation level.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message