Modifies a threshold by associating it with a different threshold procedure, free-space level, or segment name. You cannot use sp_modifythreshold to change the amount of free space or the segment name for the last-chance threshold.
sp_modifythreshold dbname, segname, free_space [, new_proc_name] [, new_free_space] [, new_segname]
is the database for which to change the threshold. This must be the name of the current database.
is the segment for which to monitor free space. Use quotes when specifying the “default” segment.
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.
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.
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.
is the new segment for which to monitor free space. Use quotes when specifying the “default” segment.
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
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
For more information, see the System Administration Guide.
When a threshold is crossed, Adaptive Server executes the associated stored procedure. Adaptive Server uses the following search path for the threshold procedure:
If the procedure name does not specify a database, Adaptive Server looks in the database in which the threshold was crossed.
If the procedure is not found in this database and the procedure name begins with “sp_”, Adaptive Server looks in the sybsystemprocs database.
If the procedure is not found in either database, Adaptive Server sends an error message to the error log.
Adaptive Server uses a hysteresis value, the global variable @@thresh_hysteresis, to determine how sensitive thresholds are to variations in free space. Once a threshold executes its procedure, it is deactivated. The threshold remains inactive until the amount of free space in the segment rises to @@thresh_hysteresis pages above the threshold. This prevents thresholds from executing their procedures repeatedly in response to minor fluctuations in free space.
By default, Adaptive Server monitors the free space on the segment where the log resides and executes sp_thresholdaction when the amount of free space is less than that required to permit a successful dump of the transaction log. This amount of free space, the last-chance threshold, is calculated by Adaptive Server and cannot be changed by users.
If the last-chance threshold is crossed before a transaction is logged, Adaptive Server suspends the transaction until log space is freed. Use sp_dboption to change this behavior for a particular database. Setting the abort tran on log full option to true causes Adaptive Server to roll back all transactions that have not yet been logged when the last-chance threshold is crossed.
You cannot use sp_modifythreshold to change the free-space value or segment name associated with the last-chance threshold.
Only databases that store their logs on a separate segment can have a last-chance threshold. Use sp_logdevice to move the transaction log to a separate device.
Each database can have up to 256 thresholds, including the last-chance threshold.
Each threshold must be at least 2 times @@thresh_hysteresis pages from the next closest threshold.
Use sp_helpthreshold for information about existing thresholds.
Use sp_dropthreshold to drop a threshold from a segment.
Any user with create procedure permission can create a threshold procedure in a database. Usually, a System Administrator creates sp_thresholdaction in the master database, and Database Owners create threshold procedures in user databases.
sp_modifythreshold does not verify that the specified procedure exists. It is possible to associate a threshold with a procedure that does not yet exist.
sp_modifythreshold checks to ensure that the user modifying the threshold procedure has been directly granted the “sa_role”. All system roles active when the threshold procedure is modified are entered in systhresholds as valid roles for the user writing the procedure. However, only directly granted system roles are activated when the threshold fires. Indirectly granted system roles and user-defined roles are not activated.
Adaptive Server passes four parameters to a threshold procedure:
@dbname, varchar(30), which identifies the database
@segment_name, varchar(30), which identifies the segment
@space_left, int, which indicates the number of free pages associated with the threshold
@status, int, which has a value of 1 for last-chance thresholds and 0 for other thresholds
These parameters are passed by position rather than by name; your threshold procedure can use other names for them, but the procedure must declare them in the order shown and with the correct datatypes.
It is not necessary to create a different procedure for each threshold. To minimize maintenance, create a single threshold procedure in the sybsystemprocs database that can be executed by all thresholds.
Include print and raiserror statements in the threshold procedure to send output to the error log.
Tasks that are initiated when a threshold is crossed execute as background tasks. These tasks do not have an associated terminal or user session. If you execute sp_who while these tasks are running, the status column shows “background”.
Adaptive Server executes the threshold procedure with the permissions of the user who modified the threshold, at the time he or she executed sp_modifythreshold, minus any permissions that have since been revoked.
Each threshold procedure uses one user connection, for as long as it takes to execute the procedure.
WARNING! System procedures cannot provide accurate information about space allocation when free-space accounting is disabled.
Use the no free space acctg option of sp_dboption to disable free-space accounting on non-log segments.
You cannot disable free-space accounting on log segments.
Only the Database Owner or a System Administrator can execute sp_modifythreshold.
Commands create procedure, dump transaction
System procedures sp_addthreshold, sp_dboption, sp_dropthreshold, sp_helpthreshold, sp_thresholdaction
