To prevent the transaction log from filling up, it must be truncated periodically. You can do this either by enabling the trunc. log on chkpt. option or by regularly executing the DUMP TRANSACTION statement. Even if the trunc. log on chkpt. option is enabled, you might have to explicitly execute DUMP TRANSACTION statements during peak periods to prevent the log from filling up.
Because the trunc. log on chkpt. option causes the equivalent of the DUMP TRANSACTION WITH TRUNCATE_ONLY statement to be executed, it truncates the log without saving it to a device. Use this option only on databases for which transaction log dumps are not being saved (typically development systems or systems where only full database dumps, and not DUMP TRANSACTION, will be used).
If you are in a production environment and using DUMP TRANSACTION to truncate the log, time the statements close enough together so that no process ever receives the 1105 error (which indicates that the log is full). It is possible to automate this process. For details, see "Automating Transaction Log Dumps," later in this chapter.
When you execute a DUMP TRANSACTION statement, transactions completed prior to the earliest outstanding transaction or the earliest transaction marked for replication but not yet moved to the distribution database are truncated from the log, unless they are on the same log page as the last outstanding transaction. All transactions since the earliest outstanding transaction are considered active and are not truncated, even if they have completed.
The following diagram illustrates that all transactions after an outstanding transaction are considered active. Note that because pages are reallocated as necessary, page numbers do not necessarily increase over time.
Because the DUMP TRANSACTION statement truncates only the inactive portion of the log, you should be careful not to allow stranded (uncommitted) transactions to remain in the log for a long time.
For example, suppose a user issues a BEGIN TRANSACTION statement but never commits the transaction. Nothing logged after the BEGIN TRANSACTION can be purged out of the log until one of the following events occurs:
Stranded transactions are usually due to application problems.