SQL Anywhere database replication - Resolving Operation too large error in SQLRemote

Author

Resolving Operation too large error in SQLRemote

Jason Davenport

2005-11-25, 7:23 am

Apologies - lengthy Post...

DBRemote questions - running SQL Anywhere 7.04 Message
agent.
2-way synchronisation between 1 Consolidated database and 38
Remote databases.

We recently recevied an "Opeartion too large" error running
dbremote when passing through a large stored procedure. To
rectify this problem we set the message size limit to using
-l switch to 200(Kb) on our consolidated DB, the remote DBs
have then have had their corresponding message agent size
limit increased to 200(Kb) to allow the large message to be
flushed.

The next sycnhronisation at the remote DB has resulted in a
"Deleting corrupt message". We then set the message agent to
run with the default limit of 50(Kb), this resulted in a
"Missing message" error and the remote now appears to be
synchronising again to the consolidated.

However, when we run the message agent on the cconsolidated
with the default message size limit 50(Kb) we still are
getting the "Opeation too large" message, my questions
are.... a) Is this message still stuck in the system
because we have other remote databases still running the
message agent with the default 50(Kb) limit that we haven't
yet modified, and b) Can we change this size limit in
DBremote as we have done without any knock-on consequences.

Many thanks in advance of any replies

Jason

Reg Domaratzki \(iAnywhere Solutions\)

2005-11-25, 9:23 am

Note : A lengthy post with lots of information is better than a short post
without enough information to help.

The "Deleting Corrupt Message" should only occur if you legitimately have a
corrupt message (not likely) or if you are running dbremote with -l Xk (if
no switch assume X=50), and there exists a message in the message system
that after being uncompressed is greater than X kb. I'm not convinced that
your remote database was running with -l 200k when it attempted to apply the
messages generated by the consolidated when it ran with -l 200k. The
missing message also tells me that it never successfully applied the
operation that caused problems, and the fact that the consolidated is
complaining about operation too large again also tells me that dbremote has
not successfully applied this operation at all your remote sites either.

Let's assume for a minute that the operation that was too large (i.e. did
not fit in a 50Kb message) was at offset X. Let's also assume the publisher
of the consolidated database is called "cons" and the name of the remote
user is question is "rem1". At the "rem1" database, where corrupt then
missing messages were reported, we need to check whether the operation has
been applied yet. Execute the following SQL :

select log_received from SYS.SYSREMOTEUSERS where user_name = 'cons';

If the value returned is less than X, the operation has not been applied
yet. If it's greater than X, it has been applied.

On the consolidated, we can check and see which remote users have already
applied the operation with the following SQL :

select user_name from SYS.SYSREMOTEUSERS where confirm_received > X;

Until this statement returns no rows, there are still remote users that need
to apply the operation at X, and you need to continue to run dbremote with
the -l 200k switch.

This leads me to my final question, which is why you would ever decrease the
size of the messages once you've increased it to 200k? There is no harm in
increasing the message size. I would have increased the message size once
at all locations, and then left it. There is no need to drop it back to the
default size of 50K once you've increased it. It's just a maintenance
nightmare IMHO, and the fact that your errors are continuing to occur is
proof of that. I'd suggest you increase the message size to 500k at all
locations so you should never need to worry about this again.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<Jason Davenport> wrote in message
news:4386f909.8b3.1681692777@sybase.com...
> Apologies - lengthy Post...
>
> DBRemote questions - running SQL Anywhere 7.04 Message
> agent.
> 2-way synchronisation between 1 Consolidated database and 38
> Remote databases.
>
> We recently recevied an "Opeartion too large" error running
> dbremote when passing through a large stored procedure. To
> rectify this problem we set the message size limit to using
> -l switch to 200(Kb) on our consolidated DB, the remote DBs
> have then have had their corresponding message agent size
> limit increased to 200(Kb) to allow the large message to be
> flushed.
>
> The next sycnhronisation at the remote DB has resulted in a
> "Deleting corrupt message". We then set the message agent to
> run with the default limit of 50(Kb), this resulted in a
> "Missing message" error and the remote now appears to be
> synchronising again to the consolidated.
>
> However, when we run the message agent on the cconsolidated
> with the default message size limit 50(Kb) we still are
> getting the "Opeation too large" message, my questions
> are.... a) Is this message still stuck in the system
> because we have other remote databases still running the
> message agent with the default 50(Kb) limit that we haven't
> yet modified, and b) Can we change this size limit in
> DBremote as we have done without any knock-on consequences.
>
> Many thanks in advance of any replies
>
> Jason

Jason

2005-11-28, 7:23 am

Reg,

Thanks for your words of wisdom, you have answered my
questions, just to let you know...

We are going to run your recommended steps to clear down
those remotes that are still not receiving messages that are
being held up by this large message.

Finally, we were not in a position to change the application
which is hardcoded to run the message agent at the default
50Kb limit, so I was just confirming that there no adverse
affects by increasing and then decreasing the limit.

Many thanks again

Jason

Sponsored Links