Skip to Content
Author's profile photo Breck Carter

Controlling The SQL Anywhere Transaction Log Size

If you have any SQL Anywhere databases where the transaction log is growing too fast, you may be interested in this blog post:

Controlling The SQL Anywhere Transaction Log Size

Overview

The problem of a rapidly growing SQL Anywhere transaction log filling up the hard drive is discussed. The Foxhound High-Frequency Insert-Delete database is used as the primary example and a non-High-Frequency Insert-Delete benchmark database is used as a secondary example. The Introduction describes the problem, and several solutions are presented:

  • Introduction
  • Method 1: Get rid of the log altogether with dblog -n
  • What about restoring the database?
  • Method 2: Automatically truncate the log on every checkpoint with dbsrv -m
  • Method 3: Regularly truncate the log with dbbackup -xo
  • Method 4: Regularly truncate the log with BACKUP … TRUNCATE;
  • Method 5: Regularly make a full backup with dbbackup -x
  • Method 6: Do #5, then make incremental log backups with dbbackup -n -t -x
  • Method 7: Use a combination of methods
  • Recommendations
  • External References
  • Bonus Tip

The recommended solution is 2-plus-5 for a Foxhound database, and for a non-Foxhound database the best solution depends on the answers to questions like “Do you care about your data?”.

The following topics are not discussed:

  • Databases which participate in SQL Remote replication, because the preservation of old transaction log entries is so important and controlling the file size is a whole different topic.
  • Databases which participate as MobiLink remote databases, for the same reason as SQL Remote.
  • Databases which participate in SQL Anywhere High Availability (mirror) and Read-Only Scale-Out setups, because… well… there just wasn’t time.
  • Databases with auditing = ‘On’ and audit_log = ‘TRANSLOG’ because the details are recorded in the transaction log.
  • The brute force “Method Zero” (stop database, delete log, start database) because… well… that pretty much covers Method Zero.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.