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.

SQL: Sybase transaction log

Each user's connection, has 2KB of memory assigned, that should be used as a buffer. It is called User Log Cache (ULC). It can be said, that transaction is played in the ULC first. If transaction is short, than it fits fully into the ULC, but if it is too long, than the ULC would fill up...

So whenever COMMIT is issued by the transaction or if the ULC becomes full, Sybase sends the modified pages into the database's transaction log.

At that time, we can say, that transaction log is more important as the database itself. Every committed transaction is saved in the log first. The data file can be not modified at all! Let's analyze, what happens when user modifies some data:

• data is read form the disk into the buffer
• user transaction begins
• transaction data modifications are done and stored in ULC
• finally, the transaction issues the COMMIT
• data from ULC is sent to transaction log
• when the 'save on disk' operation finishes the user application can continue, as at that moment we are sure, that the transaction is fully stored on the disk, data modification is also done in memory buffer, where the "up to date" pages are stored
• ...
• at some moment a CHECKPOINT is issued
• CHECKPOINT synchronizes the data from buffer cache to disk

SQL: Transactions in Sybase

Transaction is one or more operations on data that need to be saved in the database all or none of them. Transactions can be introduced in Sybase in two ways:

• chained - non default, compliant with ANSI standard. The specific of that mode is, that transaction is started automatically when the first statement modifying the data is started. To finish the transaction COMMIT or ROLLBACK must be issued. This model is default in Oracle database.
• unchained - default, non ANSI standard. Each command modifying the data is a complete transaction. That transaction is started automatically and also automatically committed when the data modification finishes. To have more commands in one transaction an explicit BEGIN TRANSACTION must be issued. To finish the transaction COMMIT or ROLLBACK must be executed. This model is default in TSQL languages (Sybase or Microsoft SQL).