I have a transaction log file that is about 800 MB in size. If I use the
dbtran utility it generates a plain ASCII SQL file of about 15 MB in size!

I thought, that the trx log is compressed and so I am surprised why it
is so much smaller.

My question: Why is such a big trx file generated for lousy 40000
insert, update and delete statements?? Do I have misconfigured my server??

The bad thing: When I run the daily backup tha ASA server sometimes
crashes with an exception caused by a page allocation error. This only
happens randomly but always if there is such a big log file.

Any suggestions what the problem could be?

Tom

be sure to translate everything

- Trigger actions can recurse. [see the -Z or -T switches]

- Rollbacks can accumulate. [see -A switch]

- Auditing can add a lot of overhead [see -G switch]

- SQL Remote can double the size of updates and
and using lot of publications can add overhead to
every statement [see -SR for SQL Remote and dbmlsync]

- Some dbtran switch can filter out a lot of information
[ see -t, -ir, -is, -it, -f, -j, -u, or -x ] so remove any of
those you may be using.

Do you do ever use LOAD TABLEs with
either CONTENT or ROW logging? If so that
can fatten up a transaction log.

I don't believe an account restrictions should apply
but definitely go with the dba account.

There is a way to cause excessive log growth by
issusing an ALTER DBSPACE TRANSLOG statement.
If that happens your backups of the transaction log files
should be much smaller.

Also look for large jumps in the offset in the translated
SQL files. Those represent hidden information of
some sort.

hope this helps you investigate this deeper


"at" <"pub (at) bednarz.ch"> wrote

Nick,

Thanks a lot for that information. I do not use SQLRemote but tons of
triggers.

I will look into all the switches in detail and come back if I have any
further problem.

Cheers

Tom


Nick Elson [Sybase iAnywhere] schrieb:

Also look for tables that do not have a primary key or unique
constraint. Logging updates and deletes on such tables requires writing
all column values for the affected row. Instead of logging

UPDATE tbl SET col=[new value] WHERE pk_col=[pk value]

we must log

UPDATE FIRST tbl SET col=[new value] WHERE col1=col1_value AND
col2=col2_value AND ... AND coln = coln_value.

If you have a table without a PK, you might have seen performance
warning in the server window too.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

at wrote: