Controlling The SQL Anywhere Transaction Log Size

[former_member428492]

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.