In Sybase, what can I do to figure out why my Transaction Log is filling up?

Question

We have a process that runs each morning to load about 40,000 rows from one server to another. In the past few weeks, the process has been having a 'log suspend' once or twice a week. This is preceded by a "log segment has fallen critically low" message.

I am a programmer and not a DBA, but our DBA has said the problem is on the programming end. But since neither the process nor the number of rows has changed, I think it's something on the database side.

The DBA said he increased the size of the transaction log a few times and that's not solving the problem.

Does anyone have any thoughts where I should start?

Answer 1

The transaction log is where Sybase stores all changes made to a database. It can be dumped separately from the database. Dumping the transaction log basically provides some kind of incremental backup functionality.

The first thing you need to figure out is whether you need the transaction log on this database i.e. is it OK for you if all data created since the last database dump are lost in case of a crash. If it is the case, a solution would be to enable truncation of the transaction log on checkpoint. If you need the transaction log, you'll have to create a threshold action to dump it to a file. This will empty it and you can proceed.