Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    6

    Unanswered: Transaction Log not getting cleared ASE 11.9.2

    Transaction Log not getting cleared from my ASE 11.9.2
    If I give dump tran also the log is not getting cleared.
    So the log segment is increasing and I had add new segments.

    So what’s the solution for this.

  2. #2
    Join Date
    Aug 2004
    Posts
    38
    It sounds like you may have an open transaction, or a replication truncation marker that isn't being moved along. Is the database replicated or was it ever replicated in the past?

    Both of these will show up in:

    select * from master..syslogshold where dbid= <your dbid>

    What options are set on this database and what command are you using to dump the transaction log?

  3. #3
    Join Date
    Feb 2005
    Posts
    6
    Quote Originally Posted by KevR
    It sounds like you may have an open transaction, or a replication truncation marker that isn't being moved along. Is the database replicated or was it ever replicated in the past?

    Both of these will show up in:

    select * from master..syslogshold where dbid= <your dbid>

    What options are set on this database and what command are you using to dump the transaction log?
    Hi

    Thanks for the reply.

    ya the replication server was there in the past
    but now the replication is not there
    the command was dump tran <db name> with no_log

  4. #4
    Join Date
    Aug 2004
    Posts
    38
    If dump tran with no_log isn't clearing it down then it's almost certainly an open transaction or a truncation marker that's the problem.

    What did the select from syslogshold return?

    If there's a user transaction in there you'll need to get it to commit before you can truncate the log. If this isn't possible you will have to kill the process and you will be able to truncate the log once it has rolled back.


    If it's a replication marker left over from when the database was replicated, use the database and issue:

    dbcc settrunc(ltm,ignore)

    You should now be able to truncate the log. This is assuming that you'll never want to replicate the transactions that were in there.

    It's also worth checking that your dump trans aren't being blocked by a hanging database/transaction log dump. This happens from time to time, but I'm sure that you would have spotted it if this was the case.


    BTW, dump tran with truncate_only is preferable to no_log, as no_log can cause recovery problems if you're unlucky enough for the server to crash while the command is running.

  5. #5
    Join Date
    Feb 2005
    Posts
    6
    Quote Originally Posted by KevR
    If dump tran with no_log isn't clearing it down then it's almost certainly an open transaction or a truncation marker that's the problem.

    What did the select from syslogshold return?

    If there's a user transaction in there you'll need to get it to commit before you can truncate the log. If this isn't possible you will have to kill the process and you will be able to truncate the log once it has rolled back.


    If it's a replication marker left over from when the database was replicated, use the database and issue:

    dbcc settrunc(ltm,ignore)

    You should now be able to truncate the log. This is assuming that you'll never want to replicate the transactions that were in there.

    It's also worth checking that your dump trans aren't being blocked by a hanging database/transaction log dump. This happens from time to time, but I'm sure that you would have spotted it if this was the case.


    BTW, dump tran with truncate_only is preferable to no_log, as no_log can cause recovery problems if you're unlucky enough for the server to crash while the command is running.

    Thanks KerV.
    I will just try and come back to u if the problem is still there

  6. #6
    Join Date
    Feb 2005
    Posts
    6
    Hi KerV

    dbid reserved spid page xactid masterxactid starttime name
    ----------- ----------- ----------- ----------- ------ ------------ --------- ----
    8 0 0 61434 0x000000000000 0x000000000000 Jan 1 1900 12:00AM $replication_truncation_point


    this is the result from ur query

    so now wt to do, can u just help me

    with regds
    George

  7. #7
    Join Date
    Dec 2004
    Posts
    25
    Just answering for KerV...

    That's definitely a replication truncation point. As KerV has already mentioned...

    use <db>
    go
    dbcc settrunc(ltm,ignore)
    go
    checkpoint
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •