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!

asked 23 Sep '10, 11:39

Breck%20Carter's gravatar image

Breck Carter
27.4k424585837
accept rate: 21%

1

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.

(23 Sep '10, 12:49) Volker Barth

@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.

(23 Sep '10, 19:01) Breck Carter
Comment Text Removed

@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).

(29 Sep '10, 10:21) Volker Barth

@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.

(29 Sep '10, 11:59) Breck Carter

This is just an incidental use, not it's primary indented purpose, but I often use -m in preparation for stowing a copy of a database for future reference or for distribution, in order to collapse the size of the log file. I just take the copy of the database and log files, start them with -m, and as soon as the server comes up, stop it. VoilĂ !

(Note the usual caveats on when it's not okay to use -m.)

permanent link

answered 03 Nov '10, 04:46

Dan%20Konigsbach's gravatar image

Dan Konigsbach
455101220
accept rate: 0%

1

Whenever a database is properly shut down, you can just delete the log file. You don't need to have the server do it for you.

(03 Nov '10, 18:25) John Smirnios

@John: Thanks for the clarification - I had thought "-m" is a nice trick but had forgotten that I regularly just do as you recommend, i.e. to throw away the log file of a shutdown database for tests and the like - silly me:)

(03 Nov '10, 22:14) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×241

question asked: 23 Sep '10, 11:39

question was seen: 824 times

last updated: 03 Nov '10, 04:46