How to clear database log file entries?

[Former Member]

When I create a database (with Sybase Central), a log file is also created. How can I clear the log file entries through Sybase Central or iSQL? Thanks.

[VolkerBarth · Accepted solution]

If you don't need the log file for different purposes (backup, SQL Remote, MobiLink clients, ...), you can simply stop the database and just delete the log file. This should be done when there are no open transactions.

Once the database is restarted, it will create a new (and therefore "empty") log file.

Note that there is no way (at least no public way) to remove only parts of the contents of the transaction log file.

Besides that, there are several ways to limit the size of the transaction log file, cf. this FAQ.

[Former Member]

As Volker mentioned, you can delete the log file and run without one if you are not worried about backup/recover-ability of your data.

However, maintaining an intact log file can be the difference between recovering a corrupt database and starting from scratch. If you have the free space available for the transaction log, I would recommend continuing with the transaction log in place. There is also an option of using incremental logs for backup/recovery, but as always, the strategy you put in place has to be a design-choice.

Here is a link to the help for a little information on the transaction log: http://dcx.sybase.com/index.html#1201/en/dbadmin/da-dbfiles-s-4160005.html

[MCMartin]

Also as I have learned from the Sybase Gurus having no transaction logfile will be a little bit slower than having one. So my recommendation is to use a logfile.

[VolkerBarth]

AFAIK, it can be noticably slower: When you run a database without a transaction log, every COMMIT statement will force a CHECKPOINT. And when applications run in AUTO COMMIT mode, that will lead to very frequent checkpoints...

That said, I have understood (and answered) Sam's question as dealing with getting rid of the initial transaction log of a freshly created database, not of generally running without one... but that's just my understanding:)

[MCMartin]

You can also backup the database and during this process truncate the logfile. As an SQL Statement:

BACKUP DATABASE ... TRANSACTION LOG TRUNCATE

The dbbackup utility provides the command line parameter -x for this.

[thomas_duemesnil]

You can also run the database in bulkmode (-b) to avoid creation of a log file after restart of the server engine.

But don't use this in a productive Environment.