Very Computer

by **D Hol** » Wed, 18 Jul 2001 02:57:31

I'm new to SQL and even newer to replication so this is really flustrating -
I have set up a merge publication and have successfully set up two push
subscriptions, the only problem is that the replication keeps getting
stopped by Deadlocks (Error 1205). How do I stop these and can anyone tell
me what these are getting caused by?????

by **TSVK** » Wed, 18 Jul 2001 03:49:02

The first step would be stagger the schedule of merge agents and limit the
number of concurrent merges if possible.

Quote:> I'm new to SQL and even newer to replication so this is really
flustrating -
> I have set up a merge publication and have successfully set up two push
> subscriptions, the only problem is that the replication keeps getting
> stopped by Deadlocks (Error 1205). How do I stop these and can anyone
tell
> me what these are getting caused by?????

by **Daisy Hol** » Wed, 18 Jul 2001 04:43:49

I do have the merge subscriptions as concurrent subscriptions because I need the data to be as up to date as possible (and currently transactional replication does not work because of Distributed tranaction error, but this is another problem). So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Christopher John Cutt** » Wed, 18 Jul 2001 07:55:21

I too had this problem, but have effectively solved the problem by not
running the agents continuously. I created a job that cycles through each
job in the REPL-Merge category, starting them and waiting for completion
before starting the next. latency is minimal and deadlocks non existent. I
suspect there is some overhead in repeatedly starting these jobs
(sp_start_job), as opposed to allowing them to run continuously, but not
enough to cause me concern.

I do have the merge subscriptions as concurrent subscriptions because I need
the data to be as up to date as possible (and currently transactional
replication does not work because of Distributed tranaction error, but this
is another problem). So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Charle** » Wed, 18 Jul 2001 22:28:26

Daisy,

Can you describe the application you are using replication for? There maybe
other options like log shipping that may make more sense.

Charles Johnson

I do have the merge subscriptions as concurrent subscriptions because I need
the data to be as up to date as possible (and currently transactional
replication does not work because of Distributed tranaction error, but this
is another problem). So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **D Hol** » Thu, 19 Jul 2001 23:52:07

Ok, that sounds like a good idea, about how long does it take for all of the
data to be replicated? It is almost vital that the data be replicated
within less than a minute.

Thanks for the idea, I'll look into it!

> I too had this problem, but have effectively solved the problem by not
> running the agents continuously. I created a job that cycles through each
> job in the REPL-Merge category, starting them and waiting for completion
> before starting the next. latency is minimal and deadlocks non existent.
I
> suspect there is some overhead in repeatedly starting these jobs
> (sp_start_job), as opposed to allowing them to run continuously, but not
> enough to cause me concern.

> I do have the merge subscriptions as concurrent subscriptions because I
need
> the data to be as up to date as possible (and currently transactional
> replication does not work because of Distributed tranaction error, but
this
> is another problem). So I turned to Merge - so now what?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

by **D Hol** » Thu, 19 Jul 2001 23:59:43

I am using the replication to share tables for a shipping/receiving program
between three locations. (One enters the orders and the other two locations
complete the orders and receive orders) It is vital that the data be as
current as possible (ie an order is placed and the other two location need
to see it immediately). We were previously using .mdb linked tables accross
the network, but the speed was highly lacking.
I originally planned to use Transactional with immediate-update replication,
but have since given up that plan due to "technical difficulties" that
cannot seem to be resolved.

I have heard of log shipping but have absolutely no clue about it other than
the name. Do you think that log shipping would be a better option? Or do I
just have to live with the deadlocks and spend my days making sure the
replication doesn't stop?

> Daisy,

> Can you describe the application you are using replication for? There
maybe
> other options like log shipping that may make more sense.

> Charles Johnson

> I do have the merge subscriptions as concurrent subscriptions because I
need
> the data to be as up to date as possible (and currently transactional
> replication does not work because of Distributed tranaction error, but
this
> is another problem). So I turned to Merge - so now what?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

by **Charle** » Sat, 21 Jul 2001 04:19:00

D.

Log shipping will not work in your application, there is too much latency.
Your linked database approach sounds like the way you may want to proceed.
Take a look at linked servers. You could code the stored procedures or use
triggers to move the data.

Hope this helps.
--Charles Johnson

> I am using the replication to share tables for a shipping/receiving
program
> between three locations. (One enters the orders and the other two
locations
> complete the orders and receive orders) It is vital that the data be as
> current as possible (ie an order is placed and the other two location need
> to see it immediately). We were previously using .mdb linked tables
accross
> the network, but the speed was highly lacking.
> I originally planned to use Transactional with immediate-update
replication,
> but have since given up that plan due to "technical difficulties" that
> cannot seem to be resolved.

> I have heard of log shipping but have absolutely no clue about it other
than
> the name. Do you think that log shipping would be a better option? Or do
I
> just have to live with the deadlocks and spend my days making sure the
> replication doesn't stop?

> > Daisy,

> > Can you describe the application you are using replication for? There
> maybe
> > other options like log shipping that may make more sense.

> > Charles Johnson

> > I do have the merge subscriptions as concurrent subscriptions because I
> need
> > the data to be as up to date as possible (and currently transactional
> > replication does not work because of Distributed tranaction error, but
> this
> > is another problem). So I turned to Merge - so now what?

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!

Very Computer

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks

Deadlocked by Deadlocks