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
27.9k476634913
accept rate: 21%

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,153 times

last updated: 04 Aug, 08:02