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 Carter
32.5k●540●724●1050
accept rate:
20%
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!
> "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).