sp_modifythreshold

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]

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, the SAP ASE 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 SAP ASE server or on an Open Server. Thresholds cannot execute procedures on remote SAP ASE 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, the SAP ASE 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.

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
```

You cannot use sp_modifythreshold to change the amount of free space or the segment name for the last-chance threshold.
Use sp_helpthreshold for information about existing thresholds.
Use sp_dropthreshold to drop a threshold from a segment.
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.

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage database` privilege.
Disabled	With granular permissions disabled, you must be the database owner or a user with sa_role.

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

Information	Values
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

Crossing a Threshold
When a threshold is crossed, the SAP ASE server executes the associated stored procedure. The SAP ASE server uses the following search path for the threshold procedure:
The Last-Chance Threshold
By default, the SAP ASE 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 the SAP ASE server and cannot be changed by users.
Create Threshold Procedures with sp_modifythreshold
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.
Execute Threshold Procedures with sp_modifythreshold
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”.
Disable Free-Space Accounting
Use the no free space acctg option of sp_dboption to disable free-space accounting on non-log segments.

Parent topic: System Procedures

Related reference

sp_addthreshold