Transaction Log Space Management

Analyze and free transaction log space.

SAP ASE provides a single transaction log segment per database. Space can be added to a log segment or removed from a log segment. However, at any given point, there is limited space in a log segment.

Whenever the database client applications perform any data manipulation language (DML) operations on the data, SAP ASE produces log records that consume space in the transaction log. Typically there are several clients performing DMLs concurrently on the database and log records are appended to the log whenever user log caches (ULCs) for individual clients are full or in some other conditions such as when a data page is changed by multiple open transactions. Log records of several transactions are therefore typically interleaved in the transaction log space.

Removing transactions from the transaction log to free the log space can be done using dump transaction. However, there different scenarios that can cause the transaction log can grow in such a way that the dump transaction command is not able to free space. In these situations, the log consumes space to such an extent that it affects the continuous availability of the database system for any write operations. The scenarios include:

Transactions are entered into the server but not committed. In this situation the log cannot be truncated because there is an active transaction.
Replication Server is slow in reading the log which prevents truncating the log.
A dump transaction has not been performed for a long period of time. Periodically dumping transactions can keep the size of the reserved space in the log limited and ensure that there is free space available in the log which allows the space freed after dump transaction to be reused for further logging activity.

You can use the loginfo function to evaluate how the space of a transaction log is used and determine the type of actions possible to free log space. The sp_thresholdaction procedure can be used to free log space in the transaction log if the available free space falls below a preconfigured threshold. The recommended action is to define a trigger that will execute dump transaction once the log fall below the threshold. However, the dump transaction command cannot truncate the portion of the log beginning from of the oldest incomplete or active transaction in the log, since this portion is needed for recovery of the database. In this case, the oldest transactions can be aborted, depending on circumstances.

Transaction Log Space
The transaction log contains sufficient information about each transaction to ensure that it can be recovered. This transaction information can be interleaved within the log.
Automating Transaction Log Management
You can use sp_thresholdaction to identify the oldest active transaction and use sp_xact_loginfo to monitor the longest running transaction per database or abort the oldest active transaction based on conditional criteria.
Analyzing and Managing Transaction Log Space
Use the loginfo function to view and free transaction log space.
Truncating a Log that Is Not on A Separate Segment
If a database does not have a log segment on a device that is separate from data segments, you cannot use dump transaction to copy the log and then truncate it.
Truncating the Log in Early Development Environments
In early development environments, the transaction log can be quickly filled by creating, dropping, and re-creating stored procedures and triggers, and checking integrity constraints.
Truncating a Log that Has No Free Space
When the transaction log is very full, you may not be able to use your usual method to dump it.