Transaction Log full error

[Raghavendra Kulkarni]

Hi all

Every once in a couple of weeks when we run our application we seem to be
getting a transaction log full error. In fact we have ample space allocated
for the log. For some reason the database seems to freeze up as the
allocated tran log area gets gobbled up. It ultimately reaches within a
couple of mb of the allocated space and will give this error.

Is it some process within the application that is causing this to happen?
Are there are any processes to look for within Sybase that may provide
pointers as to what may be happening. There is no consistency as to why it
happens. At times everything will work great till the db gives way thus.

We are trying to port a C++ app to the web. The error occurs on both the web
app and the C++ app. There are stored procedures that are inherited from the
days of the earlier legacy applications that are used by the new web app.

Any pointers are as usual greatly appreciated.

Raghu

[Benny Pei]

Several things:

1. This query shows you all the open transactions on your server order by
starttime.

select l.spid,
db_name(l.dbid) "db name",
name "proc name",
hostname,
starttime
from master..syslogshold l, master..sysprocesses p
where l.spid=p.spid
order by starttime

2. Perhaps there is change in behavior in your application which generate extra
transaction log. You can start doing more frequent transaction log dump if this
is the case. It occured to us last time when we had the transaction log filled
up several days in a row and developers never admitted that they changed
anything. After a few days of poking around the server and logs, it tunred out
to be a bug in one of the stored procedures.

3. dbadevil.com has a log analyzer tool to look what is in the transaction log.

Hope it helps.

benny

[Ivan Santhumayor]

If its from a stored procedure, try dropping/recreating proc. I have seen a
problem, where the stored proc would gobble up the tran log in no time. It
somehow was due to a bug with the access path of some complex query the proc was
running. Rebuilding the proc, solved our problem.