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 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.