Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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 '18, 23:05

Breck%20Carter's gravatar image

Breck Carter
32.5k5417271050
accept rate: 20%

edited 03 Aug '18, 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 '18, 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 '18, 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:

×50
×48

question asked: 02 Aug '18, 23:05

question was seen: 2,226 times

last updated: 04 Aug '18, 08:02