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:

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.