DUMP TRANSACTION - command and options

All the transactions before being saved in the database, first need to be put in the transaction log. Of course with time the transaction log would fill up, so the old, closed transactions need to be removed from the log. To do this you can choose one of the solutions:

• set database option 'trunc log on chkpt', what is good on test systems, but not in production. Why? Because on test systems we don't care about the data itself, while on production we would like to have possibility to restore the database to any point in time, so we need to have all the transactions somewhere. In case the option 'trunk log on chkpt' has been set, when Sybase saves data in the database (checkpoint finishes) all the transactions already saved and closed by users can be and are removed from the log. So the log can fill up only if someone started a really long or huge transaction.
• from time to time perform log dump, and monitor the space in the logs. 

The syntax for log backup is:

dump tran <db_name> to <device_name>
[, stripe on <device_name>...]
[with <options>]

This command usually takes all the transactions committed already by the user and saves them on the device. As the data is already saved somewhere it can be removed from the transaction log, making space for other transactions. In case the transaction log would not be executed on time, the log can become full and all the transactions may be suspended or rolled back depending on database option named  'ABORT TRAN ON LOG FULL', what was already described in Sybase log space exhausted, transactions frozen -what to do?

Let's take closer look to the options that can be added to the dump transaction command

dump tran db_name with truncate_only

This command causes the log being truncated, without being actually backed up! Use it only if you don't care about the data. You can also try this command when you have problem with the full log in the database. After truncating the log, checkpoint will be written to the log, so if you have problem with full log, this command may fail. You don't need to specify the device name, as the backup will not be done. Remember to perform new full backup of the database, after this operation, as the backup chain will be broken at this moment.

dump tran db_name with no_log

Works like the previous on - truncate_only. The only difference is, that here even the checkpoint will not be updated, so this command may succeed, even in case of a full transaction log.

dump tran db_name to device_name with no_truncate

This command should be used in an emergency case - when part of the database file is lost. All the content of the log will be backed up, but the data file will not be touched. Having this backup you will probably next restore the affected database and apply all the log dumps in sequence to restore the database to the last commited transaction state, so nothing will be lost. Log will not be truncated and nothing will be sent to the database itself, as we suspect that the database file is damaged at that time.

dump tran db_name to device_name with standby_access

This command allows to take backup of the log (probably quite frequently) and restore the log on another server used as warm standby server. This second server may be used for reporting purposes making the load on the first server lower. In this case the first main server is named a primary, while the second Sybase instance is started with the -q option and this server is named secondary. Transaction dumps contain only completed transactions and are restored from time to time on the secondary server allowing after some time to restore next dumps one by one. This mechanism is also known on other database systems as log shipping.