Setting the "truncate transaction log on checkpoint" option when creating the db

[Former Member]

To keep the db logs small, I'm using the -m switch with dbsrv12. I checked the dbinit docs to no avail but is it somehow possible to specify this attribute on db creation? (This way I can forget all about it when starting the db..:)

[regdomaratzki · Accepted solution]

No, it is not possible.

You could consider defining a database event that will truncate the transaction log when it reaches a certain size. The sample below, taken from the documentation, limits the size of the transaction log to 10 MB.

CREATE EVENT LogLimit
TYPE GrowLog
WHERE event_condition( 'LogSize' ) > 10
HANDLER
BEGIN
  IF EVENT_PARAMETER( 'NumActive' ) = 1 THEN 
   BACKUP DATABASE
   DIRECTORY 'c:\\\\logs'
   TRANSACTION LOG ONLY
   TRANSACTION LOG RENAME MATCH;
  END IF;
END;

[VolkerBarth · Accepted solution]

The real advantage over using "dbeng -m" would be that this will create a backup of the log, so you are able to recover in case of a media failure (if the backup is not damaged, as well, obviously) - whereas with -m, you risk data loss.

[Former Member · Accepted solution]

Thank you, that will do it.

[VolkerBarth · Accepted solution]

FWIW, if you do not need a backup at all and just want to truncate the log, use

BACKUP DATABASE DIRECTORY '' 
TRANSACTION LOG ONLY 
TRANSACTION LOG TRUNCATE;

in the above sample.

(Cf. my comment under Breck's anwer...)

[Breck_Carter]

Sometimes recovery takes a back seat to administration and performance issues; e.g., when an embedded database is recreated on demand and used for transient data.

You might consider using a DatabaseStart event to confirm that the database was started with -m, and if not, take some action.

AFAIK there is no easy way to determine whether or not -m is in effect, but this code seems to work in Version 12:

SELECT IF PROPERTY ( 'CommandLine' ) LIKE '%-m%' 
          THEN 'Y'
          ELSE 'N'
       END IF AS truncate_log_on_checkpoint;

truncate_log_on_checkpoint
'Y'

[VolkerBarth]

...though it could mean any database was started with -m, not necessarily the current one, right?

(Yep, autostarting several embedded databases is rather uncommon, and I'm just splitting hairs:))

[jeff_albion]

One other small caveat to such an approach would be that there are other (undocumented) server switches that also start with "-m", that you may be asked by Technical Support to start the server with for diagnostic purposes. You may want to consider scanning for the switch LIKE '%-m %' instead.

[Former Member]

Thank you all for the insight.

[Breck_Carter]

Since -m is a server option, not a database option, I assume -m applies to all databases named on the command line. However, I don't know what happens to a subsequent START DATABASE command that doesn't have the WITH TRUNCATE AT CHECKPOINT clause... does the command line -m apply to that database as well? I would put money on "yes" since that would obey the rule "Watcom does things the way they should be done.

[Breck_Carter]

Yes indeed... plus, if one thought it possible to code -m at the end of a command line, the predicate should be extended to cover that case:

IF PROPERTY ( 'CommandLine' ) LIKE '%-m %' OR PROPERTY ( 'CommandLine' ) LIKE '%-m'

[Breck_Carter]

Many bugs are the result of unsplit hairs... splitting hairs is a job requirement 🙂

[VolkerBarth]

FWIW, like "-n", "-m" does exist both as database server and as database option, so its meaning is influenced by its position on the command line...

[Breck_Carter]

I live and learn... perhaps a more reliable (albeit brutish) solution would be to compare the transaction log file size before and after doing an explicit CHECKPOINT 🙂

[VolkerBarth]

I guess the reliable solution is to get rid of "-m" and use a method to backup and truncate the transaction log, as Reg has suggested - at least Henginy seems to be content with that:)

Af if you really don't need to be able to recover, the following backup statement variant would just omit the backup, just truncate:

BACKUP DATABASE DIRECTORY '' 
TRANSACTION LOG ONLY 
TRANSACTION LOG TRUNCATE;

Apparently, that could be used in Reg's event sample, too.