Vendor announcement: Controlling The SQL Anywhere Transaction Log Size

[Breck_Carter]

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 🙂

[VolkerBarth]

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!

[Breck_Carter]

> "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).