Analyzing and Managing Transaction Log Space

Use the loginfo function to view and free transaction log space.

The system administrator 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 space.

This example uses loginfo to show the transaction log at a particular point in time:

======================================
select loginfo(dbid, 'database_has_active_transaction') as has_act_tran,
       loginfo(dbid, 'oldest_active_transaction_pct') as Act_log_portion_pct,
       loginfo(dbid, 'oldest_active_transaction_spid') as OA_tran_spid,
       loginfo(dbid, 'can_free_using_dump_tran') as dump_tran_free_pct,
       loginfo(dbid, 'is_stp_blocking_dump') as is_stp_blocking,
       loginfo(dbid, 'stp_span_pct') as stp_span_pct

has_act_tran OAtran_spid Act_log_portion_pct dump_tran_free_pct is_stp_blocking stp_span_pct log_occupied_pct
------------ ------------ ------------------- ------------------ --------------- ------------ ---------------
      1           19               38                 7                 0              45            52

(return status = 0)

This shows:

has_act_tran = 1, indicates that the database currently has one active transaction.
OA_tran_spid = 19, indicates that the system process ID of the oldest active transaction in the database is 19.
Act_log_portion_pct = 38, indicates that 38 percent of the log space is occupied by the oldest active transaction.
dump_tran_free_pct = 7, indicates that 7 percent of the transaction log that can freed using dump transaction.
is_stp_blocking = 0, indicates that there is no secondary truncation point preventing the use of dump transaction to free space.
stp_span_pct = 45, indicates that there is a secondary truncation point spanning 45 percent of the transaction log.
log_occupied_pct = 52, indicates that 52 percent of the total log space is currently occupied.

The available actions are:

The first step can be to use dump transaction to free the transaction log of the 7 percent shown by dump_tran_free_pct = 7. After freeing the space using dump transaction, the output of the same query shows:


has_act_tran OAtran_spid Act_log_portion_pct dump_tran_free_pct is_stp_blocking stp_span_pct log_occupied_pct
------------ ------------ ------------------- ------------------ --------------- ------------ ---------------
      1           19               38                 0                 1              45            45

(return status = 0)

At this stage, Act_log_portion_pct = 38, indicates that 38 percent of the log space is occupied by the transaction with the system process ID of 19. You can wait for system process 19 to complete, or abort the transaction.

If you decide to abort the transaction using the kill command (with or without status only option) as a measure to rescue the log, re-issuing the same query shows:
```
has_act_tran OAtran_spid Act_log_portion_pct dump_tran_free_pct is_stp_blocking stp_span_pct log_occupied_pct
------------ ------------ ------------------- ------------------ --------------- ------------ ---------------
      0           0               0                 45                 0              0            45

(return status = 0)
```

The query shows that there are no active transaction in the system. You can free all 45 percent of the occupied log space using the dump transaction command. After dump transaction is executed, the output of the same query shows:


has_act_tran OAtran_spid Act_log_portion_pct dump_tran_free_pct is_stp_blocking stp_span_pct log_occupied_pct
------------ ------------ ------------------- ------------------ --------------- ------------ ---------------
      0           0               0                 0                 0              0            0

(return status = 0)