sp_thresholdaction

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. SAP does not provide this procedure.

Syntax

When a threshold is crossed, the SAP ASE server passes the following parameters to the threshold procedure by position:
sp_thresholdaction @dbname,
	@segment_name,
	@space_left,
	@status

Parameters

Examples

Usage

There are additional considerations when using sp_thresholdaction:
  • 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, the SAP ASE server searches for the sp_thresholdaction procedure in the database where the threshold event occurs. If it does not exist in that database, the SAP ASE server searches for it in sybsystemprocs. If it does not exist in sybsystemprocs, it searches master. If the SAP ASE 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.

For 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.

See also create procedure, dump transaction in Reference Manual: Commands.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addthreshold
sp_dboption
sp_dropthreshold
sp_helpsegment
sp_helpthreshold
sp_modifythreshold
sp_who