|
The dbsrv/dbeng -m option causes the transaction log to be truncated on every checkpoint. It is probably the easiest AND most extreme method of controlling log file growth. Consider this (true) scenario: the transaction log of a "high throughput" database grows by 6G per day... in a test... with production growth rates expected to be higher. Running with -m does not seem to affect performance too much, although there seems to be a bit of a query slow down while each checkpoint is in progress... and -m certainly does eliminate log file growth. OK, so the database is not protected against the physical loss of the database file, that's a given as a "con". And no, this won't work for a SQL Remote or MobiLink application. Are there any other cons? Suppose the server crashes... will the normal starup recovery process work OK? (assuming the database and log files are readable). Any and all comments and replies are welcome! |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 23 Sep '10, 11:39
question was seen: 1,829 times
last updated: 03 Nov '10, 04:46
Well, I don't administrate such a high throughput system, but I guess I would prefer a backup with truncate log n times a day over -m - probably just because then I would not have to wonder which circumstances would prevent a recovery. With -m, you might have to go back to the last log backup in case the database file itself gets corrupted, and the log backup may be (hours) old.
@Volker: It's an embedded application (Foxhound) where I want to give the client a choice of mechanisms whereby the disk space growth can be controlled. FWIW the 6G per day growth rate is for monitoring 100 databases with 10 connections each. I cannot imagine this data being mission-critical for anyone, so a certain level of risk is probably acceptable. But... I want to understand the risk.
@Breck: I assume that "in-memory mode" (checkpoint-only) would be an interesting option here - wouldn't it require a separate license... (Agreed, that doesn't answer your question).
@Volker: In this particular case too much data is preserved in the database to consider in-memory mode. This is a high-throughput system in the sense that data is rapidly inserted and rapidly deleted, but any given row may persist for months.