Cannot dump the transaction log

Posted on 2005-05-11
Last Modified: 2008-02-01
The log got full and is in suspended state.
I cannot dump the log it is giving me the following error:
1> dump tran report_data with truncate_only
2> go
DUMP TRANSACTION for database 'report_data' could not truncate the log. Either
extend the log using ALTER DATABASE ... LOG ON command or eliminate the oldest
active transaction in database 'report_data' shown in syslogshold table.

I got the oldest active transaction log:
1> select * from syslogshold
2> go
 dbid   reserved    spid   page        xactid         masterxactid
 ------ ----------- ------ ----------- -------------- --------------
      6           0    193      763512 0x000ba6780003 0x000000000000
                May  7 2005 12:27PM

(1 row affected)

How do I correct this ,thank you.
Question by:gyans
    LVL 24

    Assisted Solution

    by:Joe Woodhouse
    Most forms of DUMP TRAN themselves write to the transaction logs, if nothing else because they issue a CHECKPOINT and that writes a log record.

    What your error message is saying is that there isn't even enough room in the logs to write the DUMP TRAN ... WITH TRUNCATE_ONLY.

    The next step (per the Sybase ASE Troubleshooting Guide) is to try DUMP TRAN ... WITH NO_LOG. Warning - this is last resort only!!

    If this fails then your only option, as the error message suggests, is the "non-environmentally friendly option" of making the transaction logs bigger. (If that's not something you're familiar with, you do that via possibly "disk init" to create a new device, and then ALTER DATABASE.)

    This is a problem that will happen again unless you do something about it, so it's definitely worth your time (once you've fixed the immediate crisis) to look into what filled the log up. 99% of the time it's poor code issuing a long-running and/or large transaction. These can be fixed.

    Good luck!
    LVL 1

    Accepted Solution

    just addation to Joe's comment, to avoid this situation in the future change the
    "abort tran on log full"  option in your database to true

    sp_dboption dbname,"abort tran on log full",true

    or you can turn "trunc log on chkpt" option to true, but that mean you cannot dump the transaction, and you'll need regular "dump database" to ensure recoverablity

    So, to sum it up, if i were you i'll do the following

    1) increase the log by even 1 MB using alter database dbname on logdevxx=1
    2) directly after that i'll dump transaction with no_log
    3) change the  database option sp_dboption dbname,"trunc log on chkpt", true

    If you don't have any more space to increase the log size, then kill the process you found in the syslogshold, it'll take sometime but it'll free you some space

    If this didn't work then you'll need to restart your server

    Good Luck

    Waleed Saber
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    We should add to Waleed's comment that the "trunc log on chkpt" option means it's no longer possible to do any regular transaction dumps (ie incremental backups). Both DUMP TRAN ... WITH TRUNCATE_ONLY and WITH NO_LOG also disable incremental backups until the next full database dump is done.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Outlook Free & Paid Tools
    In the modern office, employees tend to move around the workplace a lot more freely. Conferences, collaborative groups, flexible seating and working from home require a new level of mobility. Technology has not only changed the behavior and the expe…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now