Skip to Content
author's profile photo Former Member
Former Member

Sybase ASE transaction log's not clearing

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 15, 2015 at 01:38 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 15, 2015 at 10:12 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 15, 2015 at 10:04 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 15, 2015 at 10:17 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 15, 2015 at 11:10 AM

    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)

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.