sp_modifythreshold

Description

Modifies a threshold by associating it with a different threshold procedure, free-space level, or segment name.

Syntax

sp_modifythreshold dbname, segname, free_space
	[, new_proc_name][, new_free_space][, new_segname]

Parameters

dbname

is the database for which to change the threshold. This must be the name of the current database.

segname

is the segment for which to monitor free space. Use quotes when specifying the “default” segment.

free_space

is the number of free pages at which the threshold is crossed. When free space in the segment falls below this level, Adaptive Server executes the associated stored procedure.

new_proc_name

is the new stored procedure to execute when the threshold is crossed. The procedure can be located in any database on the current Adaptive Server or on an Open Server. Thresholds cannot execute procedures on remote Adaptive Servers.

new_free_space

is the new number of free pages to associate with the threshold. When free space in the segment falls below this level, Adaptive Server executes the associated stored procedure.

new_segname

is the new segment for which to monitor free space. Use quotes when specifying the “default” segment.

Examples

Example 1

Modifies a threshold on the “default” segment of the mydb database to execute when free space on the segment falls below 175 pages instead of 200 pages. NULL is a placeholder indicating that the procedure name is not being changed:

sp_modifythreshold mydb, "default", 200, NULL, 175

Example 2

Modifies a threshold on the data_seg segment of mydb so that it executes the new_proc procedure:

sp_modifythreshold mydb, data_seg, 250, new_proc

Usage

You cannot use sp_modifythreshold to change the amount of free space or the segment name for the last-chance threshold.


Crossing a threshold


The last-chance threshold


Other thresholds


Creating threshold procedures


Executing threshold procedures


Disabling free-space accounting

WARNING! System procedures cannot provide accurate information about space allocation when free-space accounting is disabled.

Permissions

The permission checks for sp_modifythreshold differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage database privilege.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

Documents For more information, see the System Administration Guide.

Commands create procedure, dump transaction

System procedures sp_addthreshold, sp_dboption, sp_dropthreshold, sp_helpthreshold, sp_thresholdaction