sp_thresholdaction

Description

Executes automatically when the number of free pages on the log segment falls below the last-chance threshold, unless the threshold is associated with a different procedure. Sybase does not provide this procedure.

Syntax

When a threshold is crossed, Adaptive Server passes the following parameters to the threshold procedure by position:

sp_thresholdaction @dbname,
	@segment_name,
	@space_left,
	@status

Parameters

@dbname: is the name of a database where the threshold was reached.

@segment_name: is the name of the segment where the threshold was reached.

@space_left: is the threshold size, in logical pages.

@status: is 1 for the last-chance threshold; 0 for all other thresholds.

Examples

Example 1

Creates a threshold procedure for the last-chance threshold that dumps the transaction log to a tape device:

create procedure sp_thresholdaction 
        @dbname varchar(30),
        @segmentname varchar(30),
        @space_left int,
        @status int
as
        dump transaction @dbname to tapedump1

Usage

sp_thresholdaction must be created by the Database Owner (in a user database), or a System Administrator (in the sybsystemprocs database), or a user with create procedure permission.
You can add thresholds and create threshold procedures for any segment in a database.
When the last-chance threshold is crossed, Adaptive Server searches for the sp_thresholdaction procedure in the database where the threshold event occurs. If it does not exist in that database, Adaptive Server searches for it in sybsystemprocs. If it does not exist in sybsystemprocs, it searches master. If Adaptive Server does not find the procedure, it sends an error message to the error log.
sp_thresholdaction should contain a dump transaction command to truncate the transaction log.
By design, the last-chance threshold allows enough free space to record a dump transaction command. There may not be enough space to record additional user transactions against the database. Only commands that are not recorded in the transaction log (select, fast bcp, readtext, and writetext) and commands that might be necessary to free additional log space (dump transaction, dump database, and alter database) can be executed. By default, other commands are suspended and a message is sent to the error log. To abort these commands rather than suspend them, use the abort tran on log full option of sp_dboption followed by the checkpoint command.

Waking suspended processes

Once the dump transaction command frees sufficient log space, suspended processes automatically awaken and complete.
If fast bcp, writetext, or select into have resulted in unlogged changes to the database since the last backup, the last-chance threshold procedure cannot execute a dump transaction command. When this occurs, use dump database to make a copy of the database, then use dump transaction to truncate the transaction log.
If this does not free enough space to awaken the suspended processes, it may be necessary to increase the size of the transaction log. Use the log on option of the alter database command to allocate additional log space.
As a last resort, System Administrators can use sp_who to determine which processes are suspended, then use the kill command to kill them.