Hi newsgroup,

today we are about to go crazy because of our sybase ase 11.4 on linux.

What we try to do is to simply call a stored procedure. But this stored
procedure does not return from execution as expected. After a long while
it returns telling us that the transaction logbook is full and that there
is no space left on device. We have seen different error numbers like 546,
1105, 7415.

After we have dumped the transaction logs (dump trun DB_NAME with no_log)
and increased its size we could see that the behaviour did not change.
Somehow the stored procedure fills the transaction logs. But we do not
know why! The database runs with the option are "trunc log on checkpoint".

We have also increases procedure cache. But the strange behaviour
remains...

The tempdb-Database is twice as large as our main-database.

When looking at the stored procedures source code, we cannot see any
problems. All the SP does is to delete one data set in different tables.
The source code looks like this:

delete from tabel A where id=5

delete from tabel B where id=5

delete from table C where id=5

and so on.

When manually deleting the data set from each table we run into trouble if
a table has foreign key constraints or triggers. This delete-command
returns if the transaction log is completely filled.

By the way, the stored procedures have worked for a long long time without
any problems. But only today...

Any ideas what else we can analyse?

What causes the stored procedure to completely fill the transaction log?

Any help is apreciated. Thanks in advance,

Roland




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Roland <r.DOT.reichenberg (AT) gmx (DOT) dot.de> wrote


Have any of the databases on your server been made "read-only" or
"dbo-use-only"? If so, does the procedure try to do anything with
objects in such a database? I've seen similar behavior where a
proc just kept trying to renormalize or recompile over and over
again becuase of such settings.

Another cause might be CR 275637, which was fixed in 12.0.0.6 and
12.5.0.2. "Occasionally, Adaptive Server will fail to compile a stored
procedure that creates a table using the format <owner>.<table_name>.
A "table already exists" error or 11060 error may be reported in more
recent 12.0.x versions of Adaptive Server, whilst the compilation
might endlessly loop thereby filling the log in older 12.0.x
versions."

On 7 Sep 2004 15:43:34 -0700, Bret Halford <bret (AT) sybase (DOT) com> wrote:

Hi Bret,

thanks for your answer.

I have checked our database. It has neither been made 'read-only' nor
'dbo-use-only'.

But as reported im my first posting, my stored procedure is nothing more
than 5 sql-commands like

delete from table A where id=5
delete from table B...

Executing these commands works fine until a data set is to be deleted from
a table with a foreign key constraint. In this case somehow the
transaction log is filled until no more space is left.

How can I find out why the SP fills the transaction log?

Thanks for your answer!

Roland

Roland <r.DOT.reichenberg (AT) gmx (DOT) dot.de> wrote:
Do the tables have triggers ? If so, post the code.

Did these change ?


Luc.

Although I'm not sure how FK constraints could make a difference, keep in
mind that deferred updates may consume a large amount of log space (in fact,
significantly larger than the amount of data deleted). Check the query plan
for the word 'deferred', and otherwise see the ASE PErformance & TUning
Guide where this is explained in more detail.

Another idea: are you sure there aren't any delete triggers on the table,
performing all sorts of log-filling actions? (tip: run "alter table ...
disable trigger" run that same delete action and see if it makes a
difference)

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob (AT) YOUR (DOT) SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Roland" <r.DOT.reichenberg (AT) gmx (DOT) dot.de> wrote

still no answer found...

We detected the same problemes with the database on a newly created
database device just to exclude hardware errors or somehow corrupted
database devices.

Database analysis with the dbcc-tool from sybase did not even lead to a
suspicion.

A newly created database with the same structure in
tables/constraints/procedures/triggers does not cause any trouble.

So I begin to think of a sybase ase bug.


Regards,

Roland