Chapter 2: Transact-SQL Functions

lct_admin

Description

Manages the last-chance threshold, returns the current value of the last-chance threshold (LCT), and aborts transactions in a transaction log that has reached its LCT.

Syntax

lct_admin({{"lastchance" | "logfull" | "reserved_for_rollbacks"},
	database_id
	|"reserve", {log_pages | 0 }
	| "abort", process-id [, database-id]})

Parameters

lastchance: creates a LCT in the specified database.

logfull: returns 1 if the LCT has been crossed in the specified database and 0 if it has not.

reserved_for_rollbacks: determines the number of pages a database currently reserved for rollbacks.

database_id: specifies the database.

reserve: obtains either the current value of the LCT or the number of log pages required for dumping a transaction log of a specified size.

log_pages: is the number of pages for which to determine a LCT.

0: returns the current value of the LCT. The size of the LCT in a database with separate log and data segments does not vary dynamically. It has a fixed value, based on the size of the transaction log. The LCT varies dynamically in a database with mixed log and data segments.

abort: aborts transactions in a database where the transaction log has reached its last-chance threshold. Only transactions in log-suspend mode can be aborted.

logsegment_freepages: describes the free space available for the log segment. This is the total value of free space, not per-disk.

process-id: The ID (spid) of a process in log-suspend mode. A process is placed in log-suspend mode when it has open transactions in a transaction log that has reached its last-chance threshold (LCT).

database-id: the ID of a database with a transaction log that has reached its LCT. If process-id is 0, all open transactions in the specified database are terminated.

Examples

Example 1

Creates the log segment last-chance threshold for the database with dbid 1. It returns the number of pages at which the new threshold resides. If there was a previous last-chance threshold, it is replaced:

select lct_admin("lastchance", 1)

Example 2

Returns 1 if the last-chance threshold for the database with dbid of 6 has been crossed, and 0 if it has not:

select lct_admin("logfull", 6)

Example 3

Calculates and returns the number of log pages that would be required to successfully dump the transaction log in a log containing 64 pages:

select lct_admin("reserve", 64)

-----------
         16

Example 4

Returns the current last-chance threshold of the transaction log in the database from which the command was issued:

select lct_admin("reserve", 0)

Example 5

Aborts transactions belonging to process 83. The process must be in log-suspend mode. Only transactions in a transaction log that has reached its LCT are terminated:

select lct_admin("abort", 83)

Example 6

Aborts all open transactions in the database with dbid of 5. This form awakens any processes that may be suspended at the log segment last-chance threshold:

select lct_admin("abort", 0, 5)

Example 7

Determines the number of pages reserved for rollbacks in the pubs2 database, which has a dbid of 5:

select lct_admin("reserved_for_rollbacks", 5, 0)

Example 8

Describes the free space available for a database with a dbid of 4:

select lct_admin("logsegment_freepages", 4)

Usage

lct_admin, a system function, manages the log segment’s last-chance threshold. For general information about system functions, see “System functions”.
If lct_admin(“lastchance”, dbid) returns zero, the log is not on a separate segment in this database, so no last-chance threshold exists.
Whenever you create a database with a separate log segment, the server creates a default last chance threshold that defaults to calling sp_thresholdaction. This happens even if a procedure called sp_thresholdaction does not exist on the server at all.

If your log crosses the last-chance threshold, Adaptive Server suspends activity, tries to call sp_thresholdaction, finds it does not exist, generates an error, then leaves processes suspended until the log can be truncated.
To terminate the oldest open transaction in a transaction log that has reached its LCT, enter the ID of the process that initiated the transaction.
To terminate all open transactions in a transaction log that has reached its LCT, enter 0 as the process-id, and specify a database ID in the database-id parameter.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only a System Administrator can execute lct_admin abort. Any user can execute the other lct_admin options.