The forum will be down for maintenance at some point from Friday, November 16 at 19:00 EDT until Sunday, November 18 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

If you have any 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 :)

asked 02 Aug, 23:05

Breck%20Carter's gravatar image

Breck Carter
28.1k477636916
accept rate: 20%

edited 03 Aug, 12:08

Aside: When I first read the title, I was really surprised to see a "Vendor announcement" here - Is SQL Anywhere 18 coming?

But I'm more than glad that you keep on blogging!

(03 Aug, 09:50) Volker Barth
Replies hidden

> "Vendor announcement"

It's possible that's the reason for so many views :)... but I've used that tag in the past... after all, I am a vendor, just not THE vendor (SAP).

(04 Aug, 08:01) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×48
×38

question asked: 02 Aug, 23:05

question was seen: 1,192 times

last updated: 04 Aug, 08:02