Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4305
  • Last Modified:

Cannot dump the transaction log

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.
  • 2
2 Solutions
Joe WoodhousePrincipal ConsultantCommented:
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!
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
Joe WoodhousePrincipal ConsultantCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now