Controlling The SQL Anywhere Transaction Log Size
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.