sp_xact_loginfo

sp_xact_loginfo provides the span of oldest active transaction in terms of percentage of total log space.

Syntax

sp_xact_loginfo dbid [, vcharparam1] [, vcharparam2] 
[, intparam1] [, intparam2][' span_pct] [, startpage] 
[, xact_spid] [, starttime] [, firstlog_page] [, stp_page]
[, stp_pages] [, stp_blocking] [, canfree_without_abort_pct] 
[, dump_in_progress] [, activexact] [, errorcode]

Parameters

dbid – is the database ID.
vcharparam1 – varchar parameter indicating the mode. If oldestactive, the output parameter values are indicative of oldest active transaction. If xactspanbyspid, then output parameter values reflect values of active transaction for given spid.
vcharparam2 – reserved for future use. Provide NULL as a value.
intparam1 – integer parameter1 (SPID if vcharparam1 = xactspanbyspid)
intparam2 – integer parameter2
span_pct – a value from 0 to 100. Indicates the span of transaction in percentage of total log space based on value of vcharparam1(output parameter).
startpage – page number that is the start of the active transaction in the log based on value of vcharparam1. This page will hold the begin transaction log record of the active transaction.
xact_spid – server process ID of the client having the active transaction based on vcharparam1.
starttime – start time of active transaction based on vcharparam1.
firstlog_page – server process ID of the client having active transaction based on vcharparam1.
stp_page – secondary truncation point logical page number in the log. Returns -1 if replication is not active.
stp_pages – returns the total number of log pages between the secondary truncation point and the oldest active transaction. Returns 0 if:
- Replication is not active
- There is no active transaction in the log
- There is no secondary truncation point before oldest active transaction
stp_blocking – value of 0 or 1. 1 indicates that the secondary truncation checkpoint will block some portion for truncation beyond oldest active transaction span. Meaning that secondary truncation point is in between the start of the log and the start of oldest active transaction and replication agent must catch up. 0 indicates that aborting the oldest active transaction will free transaction log space without the secondary checkpoint blocking the abort.
canfree_without_abort_pct – value from 0 to 100. Indicates the difference between startlogpagenum and startxactpagenum in terms of percentage of total log space. This portion can be truncated with the dump transaction command without aborting the oldest active transaction.
dump_in_progress – returns1 if the dump transaction command is in progress, returns 0 if no dump command is in progress. Values of output parameters firstlog_page and canfree_without_abort_pct are not reliable. (output parameter).
activexact – Boolean flag indicating that there are active transactions in the log.
errorcode – Values are:
- 0 there are no errors
- 1 insufficient permission to execute
- 2 error in opening dbtable. This could be due to various reasons including the dbid or database name given does not exist.
- 3 cannot start xls session for log scan.
- 4 there are no open transaction in the log against this database

Note: For a Mixed Log Data (MLD) database, this procedure returns values equivalent to 0 in output parameters. This procedure is not supported or meant to be used for MLD databases.