SQL: Transaction log maintnance, thresholds

As presented in: SQL: Sybase transaction log, the transaction log is used to store the transactions coming from users. Having this in mind, if the transaction log would become full, no more transactions in the database will be accepted. Database with full transaction log is not usable.

To avoid situation where the transaction log would be full, there is possibility to define thresholds, that define actions that should be started when only transaction log is filled up to some specific level. By default there is Last-Chance Threshold defined. When this threshold is met, automatic process of backup of the log is started.

In production environment, when the option "trunc log on chkpt" is set to OFF, transaction log will be emptied (truncated) only after an incremental backup. Backup of the transaction log is necessary, as the space in the transaction log may be re-used only if the server knows, that the log has been copied already somewhere.

How does it work?

SQL: Recovery and Recovery Interval

Usually under recovery many people understand the process of restoring a database after a disaster, so you take an available backup, restore it and the time when the database is being restored is called recovery. This is not the correct definition of recovery in the pure database naming convention...

Recovery is quite a normal process in a database live and and is performed quite often, not only after database failure. Actually after each server restart, when databases are attached to the server, it needs to verify if the content of a database file is consistent with the content of the transaction log. This process is named recovery.

Yes. Don't be surprised, that after server restart some databases may be found in recovery status! Just sit and wait, looking from time to time into the errorlog to see the progress. After the recovery is done, the database will be available in normal state again!

What kind of situation can be met during recovery?

• in the transaction log, a transaction is found that was committed, but still not saved in the database
• in the transaction log, a transaction is found that was not committed.