loginfo

Returns information about a transaction log.

Syntax

loginfo (dbid | dbname, option]

loginfo (dbid | dbname, option, option1]

Parameters

dbid – is the database ID.
dbname – is the database name.
option – is the specific information you need about the transaction log. Valid options are:
- active_pages – the total number of pages between the oldest transaction at the time of the most recent checkpoint, and the end of the log.
- can_free_using_dump_tran – returns a number from 0 to 100 indicating the span of transaction log which can be truncated with the dump transaction command without having to abort oldest active transaction. If there is a secondary truncation point before the start of the oldest active transaction, then this is the span in the log (in percent) between the start of the log (first log page) and the secondary truncation point. If the secondary truncation point is not before the oldest active transaction, then this is the span in the log (in percent) between the start of the log (first log page) and start of the oldest active transaction.
- checkpoint_page – returns the page number in the log that contains the most recent checkpoint log record.
- checkpoint_marker – returns the record ID (RID) in the log that contains the most recent checkpoint log record.
- checkpoint_date – returns the date of the most recent checkpoint log record.
- database_has_active_transaction – returns 0 if there are no active transactions in the log. Returns 1 if there is an active transaction in the log.
- first_page – returns the page number of the first log page.
- help – shows a message with the different options.
- inactive_pages – the total number of log pages between first_page and either stp_page or oldest_transaction, whichever comes first. This is the number of log pages that will be truncated by the dump transaction command.
- is_dump_in_progress – returns 1 if dump transaction command is in progress, returns 0 if no dump command is in progress.
- is_stp_blocking_dump – returns 1 if there is a secondary truncation point before the start of the oldest active transaction, otherwise, returns 0.
- oldest_active_transaction_date – returns the start time of oldest active transaction. Returns binary(8) number which needs to be converted to date as shown in the example below:
```
select (convert(datetime, convert(binary(8), 
    loginfo(4, 'oldest_active_transaction_date')), 109))
```
- oldest_active_transaction_page – returns the logical page number of start of oldest active transaction in the log. Returns 0 if there is no active transaction.
- oldest_active_transaction_pct – returns a number from 0 to 100 indicating the span of the oldest active transaction in percentage of total log space.
- oldest_active_transaction_spid – returns the spid of the session having the oldest active transaction in the log of the Adaptive Server.
- oldest_transaction_page – returns the page number in the log on which the oldest active transaction at the time of the most recent checkpoint, started. If there was no active transaction at the time of the most recent checkpoint, oldest_transaction_page returns the same value as checkpoint_page.
- oldest_transaction_marker – returns the RID (page number and row ID) in the log on which the oldest active transaction at the time of the most recent checkpoint, started. If there was no active transaction at the time of the most recent checkpoint, oldest_transaction_marker returns the same value as checkpoint_marker.
- oldest_transaction_date – is the at which the oldest active transaction started.
- root_page – returns the page number of the last log page.
- stp_page – returns the page number of the secondary truncation point (STP), if it exists. The secondary truncation point (or STP) is the point in the log of the oldest transaction yet to be processed for replication. The transaction may or may not be active. In cases where the transaction is no longer active, the STP by definition precedes the oldest active transaction.
- stp_span_pct – returns a number from 0 to 100 indicating the span of secondary truncation point to the end of log with respect to total log space.
- stp_pages – the total number of log pages between the STP and the oldest active transaction.
- total_pages – is the total number of log pages in the log chain, from first_page to root_page.
- until_instant_marker – is the RID (page number and row ID) of the log record associated with until_time_date.
- until_time_date – is the latest time that could be encapsulated in the dump that is usable by the until_time clause of load transaction.
- until_time_page – is the log page on which the log record associated with until_time_date resides.
- xactspanbyspid – This option is to be used only with the third parameter, which is the SPID of the task. Returns the transaction span if the SPID has an active transaction in the log. Returns 0 otherwise.
Note: For a Mixed Log Data (MLD) database, this function returns a value equivalent to 0. The new options for this function are not supported or meant to be used for MLD databases.

Examples

Example 1 – Shows how to display transaction log information.

select loginfo(dbid, 'database_has_active_transaction'),
       loginfo(dbid, 'oldest_active_transaction_pct'),
       loginfo(dbid, 'oldest_active_transaction_spid'),
       loginfo(dbid, 'can_free_using_dump_tran'),
       loginfo(dbid, 'is_stp_blocking_dump'),
       loginfo(dbid, '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            14               17                 7                 0             25            32

The function returns the transaction log information:

1 active transaction
14 is the SPID of the oldest transaction
17 percent of the log that is occupied by an active transaction
7 percent of the transaction log that can be freed by using the dump transaction command
0 blocking secondary truncation points
25 percent of the log that is occupied by the span of the secondary truncation point
32 percent of the log that is occupied

Example 2 – Returns the amount of log space that is spanned for a particular transaction.

select loginfo(dbid, ‘xactspanbyspid’, spid)
spid      log_span_pct  
----------------------
  15           2

Permissions

The user must have sa_role to execute loginfo.