Because of the way we frequently copy sqla databases around, it would be very handy if the log file did not exist. For instance, many of our customers will frequently "refresh test" with production. What is the benefit of keeping a log file? What is the downside of not having a log file and forcing sqla to "maintain the log" automatically? Thanks, Doug P.S. It would be very handy for us if we could, for instance copy "Production.db" to "Test.db" and not worry about the log file.

asked 15 Sep '11, 09:17

dejstone's gravatar image

dejstone
959395069
accept rate: 0%


Having a transaction log offers several benefits. For one thing, it allows you to do forward recovery to a particular log operation/offset. Secondly, it provides additional protection from media failure if the database file becomes corrupt (assuming, of course, that you have a backup). But possibly the most important reason is that if the database does not have a transaction log, then each and every COMMIT operation causes an implicit CHECKPOINT, flushing all modified database pages to disk. That can be very, very expensive and you will notice the performance degradation even with "test" workloads.

If a database has been shutdown cleanly, and you don't care about retaining the log, then you can delete the log file (or copy the database file without the log). In that case, the SQL Anywhere server will happily create a new log file in the same directory as the database file, and off you go.

permanent link

answered 15 Sep '11, 10:03

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

2

I suggest the "copy db file without log file" approach for test cases. However, one must make sure the previously used log files in the test database directory are deleted, as well. Otherwise you will try to use a freshly copied db file with an unfitting log file, and that fails for obvious reasons. (Been there, done that.)

So, it still might be easier to copy db and log file to a test system...

(15 Sep '11, 10:22) Volker Barth
Replies hidden
4

Also, make sure that the .db file has a "read only" file attribute on it before you delete any transaction log. This indicator is set by the database engine to flag it as cleanly shut down. Live image backups (i.e. dbbackup) and crashed databases will not have this attribute. If you delete the transaction log from a database that was not cleanly shut down, the engine will refuse to start it without some nasty cmdline switches (and possible dataloss).

(15 Sep '11, 12:52) Erik Anderson

You can use the tool dblog with option -n to remove the usage of a transaction log file from a database. And vice versa, with this tool it is also easy to change the storage location of transaction files.

permanent link

answered 15 Sep '11, 12:25

Martin's gravatar image

Martin
8.6k116151237
accept rate: 14%

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:

×34

question asked: 15 Sep '11, 09:17

question was seen: 2,007 times

last updated: 15 Sep '11, 12:52