SQL Anywhere Feedback - Agent behavior - transaction log manipulation

Author

Agent behavior - transaction log manipulation

Pavel Karady

2006-03-28, 3:24 am

Greetings brainstormers,

can't the SQL Remote Message Agent (9.0.2) behave "more smart" when he sees
transaction log manipulation *while he's running*?

For example, is the transaction log gets truncated on a remote database,
here's what the log says:

I. 03/28 09:44:59. The online log has been truncated
I. 03/28 09:45:10. Scanning logs starting at offset 0000586305
I. 03/28 09:45:10. Processing transaction logs from directory
"d:\small9\new92\"
E. 03/28 09:45:10. No off-line transaction log file found and on-line
transaction log starts at offset 0000601038.
I. 03/28 09:45:10. We are looking for a transaction log file starting at
offset 0000586305.
I. 03/28 09:45:11. Execution completed

Could the agent just, if he has *already noticed* that the tranlog has been
truncated
1. update system tables with the new offset
2. take the new offset, pack it into a bow-knoted message and fire it from a
big cannon to the consolidated db
3. continue his well-designed work?

Maybe this IS impossible... in that case, I would please someone to explain
why. Sure this is impossible - when the agent has been not connected during
the tranlog truncation process. But when he's connected all the time and
sees what's happening....?

Thanks
Pavel

Rob Waywell

2006-03-28, 11:24 am

What is your command line for DBRemote?
The most likely scenario is that you have not included the directory
location of the Old Log Files:
SQL Remote User's Guide

Utilities and Options Reference

The Message Agent
....
Syntax
{ dbremote | ssremote } [ options ] [ directory ]

....

The reason that DBRemote can't "just" update the system tables and
*skip* the missing log file is because it needs to process the transactions
that happened in that missing log file.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:4428ebd9$1@foru
ms-2-dub...
> Greetings brainstormers,
>
> can't the SQL Remote Message Agent (9.0.2) behave "more smart" when he
> sees transaction log manipulation *while he's running*?
>
> For example, is the transaction log gets truncated on a remote database,
> here's what the log says:
>
> I. 03/28 09:44:59. The online log has been truncated
> I. 03/28 09:45:10. Scanning logs starting at offset 0000586305
> I. 03/28 09:45:10. Processing transaction logs from directory
> "d:\small9\new92\"
> E. 03/28 09:45:10. No off-line transaction log file found and on-line
> transaction log starts at offset 0000601038.
> I. 03/28 09:45:10. We are looking for a transaction log file starting at
> offset 0000586305.
> I. 03/28 09:45:11. Execution completed
>
> Could the agent just, if he has *already noticed* that the tranlog has
> been truncated
> 1. update system tables with the new offset
> 2. take the new offset, pack it into a bow-knoted message and fire it from
> a big cannon to the consolidated db
> 3. continue his well-designed work?
>
> Maybe this IS impossible... in that case, I would please someone to
> explain why. Sure this is impossible - when the agent has been not
> connected during the tranlog truncation process. But when he's connected
> all the time and sees what's happening....?
>
> Thanks
> Pavel
>
>
>
>
>

Nick Elson

2006-03-28, 11:24 am

The line

I. 03/28 09:44:59. The online log has been truncated

is normal if dbremote -t -r is being used (or the backup
statement is being used to rename the transaction log).
It is only an indicatiton or signal given to dbremote (by
the server) that a rename or truncate has occured.

Since the only valid location for a renamed transaction
log is in the production directory, the first question that
must be asked is this

Is the directory "d:\small9\new92\" the same as the
one the active log is located?

If not, your dbremote cmdline is incorrect.

If so, then know who/how the log is being renamed
is important to know before we can discuss the problems
you are having. If it is dbremote -x, backup ... truncate
or a third party, they you have just broken dbremote
and nothing can automatically recover from this.

Modifying the offset to skip an (otherwise) valid (but lost)
section of transation log is absolutely the wrong thing to
do in the event you are describing.

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:4428ebd9$1@foru
ms-2-dub...
> Greetings brainstormers,
>
> can't the SQL Remote Message Agent (9.0.2) behave "more smart" when he
> sees transaction log manipulation *while he's running*?
>
> For example, is the transaction log gets truncated on a remote database,
> here's what the log says:
>
> I. 03/28 09:44:59. The online log has been truncated
> I. 03/28 09:45:10. Scanning logs starting at offset 0000586305
> I. 03/28 09:45:10. Processing transaction logs from directory
> "d:\small9\new92\"
> E. 03/28 09:45:10. No off-line transaction log file found and on-line
> transaction log starts at offset 0000601038.
> I. 03/28 09:45:10. We are looking for a transaction log file starting at
> offset 0000586305.
> I. 03/28 09:45:11. Execution completed
>
> Could the agent just, if he has *already noticed* that the tranlog has
> been truncated
> 1. update system tables with the new offset
> 2. take the new offset, pack it into a bow-knoted message and fire it from
> a big cannon to the consolidated db
> 3. continue his well-designed work?
>
> Maybe this IS impossible... in that case, I would please someone to
> explain why. Sure this is impossible - when the agent has been not
> connected during the tranlog truncation process. But when he's connected
> all the time and sees what's happening....?
>
> Thanks
> Pavel
>
>
>
>
>

Pavel Karady

2006-03-29, 3:24 am

"Rob Waywell" < rwaywell_no_spam_ple
ase@ianywhere.com> wrote in message
news:442958ac@forums
-2-dub...
> What is your command line for DBRemote?
> The most likely scenario is that you have not included the directory
> location of the Old Log Files:
>
> The reason that DBRemote can't "just" update the system tables and
> *skip* the missing log file is because it needs to process the
> transactions that happened in that missing log file.

Thanks for your answer, yes, I teach this behavior with graphical examples
so I am aware of that, but the scenario I'm (perhaps not clearly) describing
is something else, my comments to it will be in the reply to second post.

Pavel

Pavel Karady

2006-03-29, 3:24 am

"Nick Elson" < no_span_nicelson@syb
ase.com> wrote in message
news:44295f1c$1@foru
ms-2-dub...
> The line
>
> I. 03/28 09:44:59. The online log has been truncated
>
> is normal if dbremote -t -r is being used

I've checked this on my small local test databases and yes, I've had -t in
the agent configuration... I've quickly run an untouched database (log not
truncated) with -t removed from it's agent conf and truncated the log from
dbisqlc using

BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE;

(as I did before) with the agent running, but observed the same behavior
(agent says that the online log has been truncated and few seconds after
that... he dies with a smile "Execution completed").

> Since the only valid location for a renamed transaction
> log is in the production directory, the first question that
> must be asked is this
>
> Is the directory "d:\small9\new92\" the same as the
> one the active log is located?

Of course it is... I am speaking about one single transaction log, the
online production transaction log of a database only, the ONE log which the
agent hover at the end at, when he's active. I'm not taking a backup of it,
not moving anything anywhere.... just truncating the online tranlog, nothing
else.

> If it is dbremote -x, backup ... truncate
> or a third party, they you have just broken dbremote
> and nothing can automatically recover from this.

What do you mean by "to have broken dbremote"?

> Modifying the offset to skip an (otherwise) valid (but lost)
> section of transation log is absolutely the wrong thing to
> do in the event you are describing.

I'm just testing... but in this case, I don't see any lost section of a
transaction log :) Is it because I don't think of message agent as of an
ASYNCHRONOUS processor?

My idea is, can't the log truncation just behave as a "blocked process"
until the agent has sent everything away so there's NO unprocessed part of a
transaction log - and then the truncation occurs and agent updates the last
offset manually (sends the offset change message to the cons of course..)
and *continues* it's work?

I'm willing to describe the process more if needed.

Thanks so far,
Pavel

Nick Elson

2006-03-29, 11:24 am

Sorry that should have been "dbbackup -t-r" in my posting and
not dbbackup. [the dbremote -t switch is something completely
different and should be removed until you fully understand what it
does.

But given this is what you are doing

BACKUP DATABASE DIRECTORY '' TRANSACTION
LOG ONLY TRANSACTION LOG TRUNCATE;

then it is no wonder you are getting an error. NEVER TRUNCATE
[aka delete] a live transaction log.

> My idea is, can't the log truncation just behave as a "blocked process"
> until the agent has sent everything away so there's NO unprocessed part of
> a transaction log - and then the truncation occurs and agent updates the
> last offset manually (sends the offset change message to the cons of
> course..) and *continues* it's work?

NOW there is a point to be made there! Many in fact. I personally
vote for all log deletion mechanisms to fail (completely with appropriate
warnings) if there is any synchronization or replication enabled on the
database. The same opinion also holds for the -m switch too.

The main problem with blocking, is the determination of what is
'unprocessed' can take months to determine. Many consolidated
databases have transaction logs around for weeks or months because
some remote has not successfully replicated for and needs those
to allow them to catch up when they due so. Blocking in that case
would cause client connections and events to appear to hang
indefinitely.

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:442a2c5c@forums
-2-dub...
> "Nick Elson" < no_span_nicelson@syb
ase.com> wrote in message
> news:44295f1c$1@foru
ms-2-dub...
>
> I've checked this on my small local test databases and yes, I've had -t in
> the agent configuration... I've quickly run an untouched database (log not
> truncated) with -t removed from it's agent conf and truncated the log from
> dbisqlc using
>
> BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG
> TRUNCATE;
>
> (as I did before) with the agent running, but observed the same behavior
> (agent says that the online log has been truncated and few seconds after
> that... he dies with a smile "Execution completed").
>
>
>
> Of course it is... I am speaking about one single transaction log, the
> online production transaction log of a database only, the ONE log which
> the agent hover at the end at, when he's active. I'm not taking a backup
> of it, not moving anything anywhere.... just truncating the online
> tranlog, nothing else.
>
>
>
> What do you mean by "to have broken dbremote"?
>
>
> I'm just testing... but in this case, I don't see any lost section of a
> transaction log :) Is it because I don't think of message agent as of an
> ASYNCHRONOUS processor?
>
> My idea is, can't the log truncation just behave as a "blocked process"
> until the agent has sent everything away so there's NO unprocessed part of
> a transaction log - and then the truncation occurs and agent updates the
> last offset manually (sends the offset change message to the cons of
> course..) and *continues* it's work?
>
> I'm willing to describe the process more if needed.
>
> Thanks so far,
> Pavel
>

Rob Waywell

2006-03-29, 8:24 pm

When you execute this statement:

BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE;

Where does your backup transaction log end up?
You have omitted the directory location in the command.

In your backup log file directory, do you have a log file that covers the
offsets 0000586305 to 0000601038?

What is your exact DBRemote command line?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:442a2c5c@forums
-2-dub...
> "Nick Elson" < no_span_nicelson@syb
ase.com> wrote in message
> news:44295f1c$1@foru
ms-2-dub...
>
> I've checked this on my small local test databases and yes, I've had -t in
> the agent configuration... I've quickly run an untouched database (log not
> truncated) with -t removed from it's agent conf and truncated the log from
> dbisqlc using
>
> BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG
> TRUNCATE;
>
> (as I did before) with the agent running, but observed the same behavior
> (agent says that the online log has been truncated and few seconds after
> that... he dies with a smile "Execution completed").
>
>
>
> Of course it is... I am speaking about one single transaction log, the
> online production transaction log of a database only, the ONE log which
> the agent hover at the end at, when he's active. I'm not taking a backup
> of it, not moving anything anywhere.... just truncating the online
> tranlog, nothing else.
>
>
>
> What do you mean by "to have broken dbremote"?
>
>
> I'm just testing... but in this case, I don't see any lost section of a
> transaction log :) Is it because I don't think of message agent as of an
> ASYNCHRONOUS processor?
>
> My idea is, can't the log truncation just behave as a "blocked process"
> until the agent has sent everything away so there's NO unprocessed part of
> a transaction log - and then the truncation occurs and agent updates the
> last offset manually (sends the offset change message to the cons of
> course..) and *continues* it's work?
>
> I'm willing to describe the process more if needed.
>
> Thanks so far,
> Pavel
>

Pavel Karady

2006-04-05, 8:24 pm

"Rob Waywell" < rwaywell_no_spam_ple
ase@ianywhere.com> wrote in message
news:442ae92b$1@foru
ms-2-dub...
> When you execute this statement:
>
> BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG
> TRUNCATE;
>
> Where does your backup transaction log end up?
> You have omitted the directory location in the command.

Yes, and I do it on a regular basis :) If the dir is omitted, then no db
file backup is taken, no log backup is taken, just renaming the tranlog or
it's truncating is performed. I've read this in this forum some (large)
while ago - and it's also documented in the help.

> In your backup log file directory, do you have a log file that covers the
> offsets 0000586305 to 0000601038?
> What is your exact DBRemote command line?

I'm absolute aware of the fact that truncating the tranlog will kill the
message agent. One more time: I do not have a problem. If I had a problem, I
wouldn't open a thread in " product_futures_disc
ussion" group.

That's why my suggestion - if the agent witnesses the truncation of log, he
should not be killed. He just should update it's sysremoteuser table with
the changed offset and send a message about this to the cons (or cons to all
remotes, if the truncation happens on cons).

Anyway, thanks for the effort
Pavel

Pavel Karady

2006-04-05, 8:24 pm

"Nick Elson" < no_span_nicelson@syb
ase.com> wrote in message
news:442aadab$1@foru
ms-2-dub...
> But given this is what you are doing
>
> BACKUP DATABASE DIRECTORY '' TRANSACTION
> LOG ONLY TRANSACTION LOG TRUNCATE;
>
> then it is no wonder you are getting an error. NEVER TRUNCATE
> [aka delete] a live transaction log.

OMG.. :)) Thanks for the info. I will never do it I promise. I've observed
this thousand times, but just for you, I will try it again. Ok here's tha
db.... yes... agent running... reps are ok... now I'm truncating the log....
NOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOOOOOOOOOOO
OOOOOOOOOOO what a surprise, the
agent went to sleep with the fishes...

> NOW there is a point to be made there! Many in fact. I personally
> vote for all log deletion mechanisms to fail (completely with appropriate
> warnings) if there is any synchronization or replication enabled on the
> database. The same opinion also holds for the -m switch too.

Oh yeah, FINALLY someone thinks that I do not have a problem, someone thinks
that I want to make a product_futures_sugg
estion! :)) Really a fight to
convince people to that.

That's a different approach than I've suggested in the starting post of the
thread, but it's also good. Truncation of the log should fail if the agent
is running - that's a good thing to consider.

> The main problem with blocking, is the determination of what is
> 'unprocessed' can take months to determine. Many consolidated
> databases have transaction logs around for weeks or months because
> some remote has not successfully replicated for and needs those
> to allow them to catch up when they due so. Blocking in that case
> would cause client connections and events to appear to hang
> indefinitely.

That's another good point, which denied my whole request - if there's at
least one single remote db that lags behind the real actions so badly, that
actions from the OLD (not online) transaction logs have not been fully
replicated, then my suggestion of tranlog truncation with sysremoteuser
table offset update is really out of the scope.

Thanks for clearing things up, for the patience and effort

Pavel

Rob Waywell

2006-04-06, 8:24 pm

How is DBRemote supposed to know that you blew away the log file on purpose?
DBRemote has to stop when a log file is missing. Creating this situation by
truncating and not backing up the production transaction log is a user
error.

If you do want to move the log offset for a remote user that isn't
replicating, then you can do that either be REVOKE REMOTE or REMOTE RESET.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:443418b4$1@foru
ms-2-dub...
> "Rob Waywell" < rwaywell_no_spam_ple
ase@ianywhere.com> wrote in message
> news:442ae92b$1@foru
ms-2-dub...
>
> Yes, and I do it on a regular basis :) If the dir is omitted, then no db
> file backup is taken, no log backup is taken, just renaming the tranlog or
> it's truncating is performed. I've read this in this forum some (large)
> while ago - and it's also documented in the help.
>
>
> I'm absolute aware of the fact that truncating the tranlog will kill the
> message agent. One more time: I do not have a problem. If I had a problem,
> I wouldn't open a thread in " product_futures_disc
ussion" group.
>
> That's why my suggestion - if the agent witnesses the truncation of log,
> he should not be killed. He just should update it's sysremoteuser table
> with the changed offset and send a message about this to the cons (or cons
> to all remotes, if the truncation happens on cons).
>
> Anyway, thanks for the effort
> Pavel
>

Breck Carter [Team iAnywhere]

2006-04-06, 8:24 pm

On 6 Apr 2006 13:44:40 -0800, "Rob Waywell"
< rwaywell_no_spam_ple
ase@ianywhere.com> wrote:

>How is DBRemote supposed to know that you blew away the log file on purpose?
>DBRemote has to stop when a log file is missing. Creating this situation by
>truncating and not backing up the production transaction log is a user
>error.
>
>If you do want to move the log offset for a remote user that isn't
>replicating, then you can do that either be REVOKE REMOTE or REMOTE RESET.

I agree that should be interpreted as a user error, and I agree with
Nick that the truncation should be prevented in the first place. SQL
Remote is touchy enough... :)

Breck

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
breck.carter@risingroad.com

Sponsored Links