Sybase ASE transaction log's not clearing

[Former Member]

We are running Sybase ASE v.11

We are not able to clear out the transaction log's using a full DB backup. We have turned on 'trunc log on chkpt', and did one manual checkpoint using SQL advantage. Then we tried taking a complete DB backup, but the transaction log's are not clearing.

We also tried restarting the server and followed the above procedure, but yet it doesn't want to work. Is there anything we are missing out.

Kindly advise.

Regards,
Ghaffar Ahmed.

[Former Member · Accepted solution]

I have resolved this issue as follows:

1. Turned ON the trun on checkpoint on db.
2. Did a checkpoint from the SQL advantage.
   1. E.g. Checkpoint  GO
3. Did a DB full DB backup.
4. After a few minutes I saw the trasaction log was cleared.

Thank you for all your help.

[jayrijnbergen]

Do you have an error message regarding your log issue?

(the first error in the server errorlog that happened indicating a space issue with the log)

In some cases you'll have to extend the log, e.g. in case the server reserved space in the log but couldn't get the actual space when it was required. (e.g. error 3475)

[hans-juergen_schwindke]

Hi,

did you execute the checkpoint in the database in question?

A full backup does not truncate the transaction log.

You should try a transaction log dump as well. In the following examples the log is truncated; subsequent tranaction log dumps to a file will not be possible. A full dump must be done first. So execute:

dump tran <dbname> with truncate_only

or (if the transaction log is "too" full):

dump tran <dbname>  with no_log

Best regards,

Juergen

[former_member188958]

Saying "v 11" is very unspecific, ASE had three 11.x versions (11.0, 11.5, 11.9) that covered a lot of ground.  It would be better to quote the @@version string.

Full database dumps "dump database" never truncate the log.

The "truncate log on checkpoint" database setting does not result in manual checkpoints truncating the log, only the automatic checkpoints issued by the checkpoint process truncate the log under this setting.

Does a manual "dump tran <dbname> with truncate_only" clear the log?

The two main possibilities that come to mind:

1)  The database may be marked for replication, but the ltm marker (log transfer marker) may not be moving.  If your master database contains a syslogshold table, is there an entry in that table for this database?  If so, what is it?  You can also check for the LTM marker directly with

dbcc traceon (3604)

dbcc gettrunc

If the LTM marker is the issue and the database really is being replicated, see if something is wrong with replication server.
If the LTM marker is the issue but the database is not meant to be replicated, you can turn off the LTM marker by running "dbcc settrunc ( ltm, ignore)." in the database.

2) Truncate log on checkpoint doesn't work if the database (or entire server) is in single user mode.

[Former Member]

Its a stand-alone server, there is no replication happening

[former_member198560]

Ahmed,

There may be possibility of presence of open transactions, which cannot be truncated/cleared. You can check the longest running open transactions from syslogshold system table in master database as below :

select * from master..syslogshold

~Gaurav