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