sp_addthreshold

Creates a threshold to monitor space on a database segment. When free space on the segment falls below the specified level, the SAP ASE server executes the associated stored procedure.

Syntax

sp_addthreshold dbname, segname, free_space, proc_name

Parameters

dbname – is the database for which to add 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.
proc_name – is the stored procedure to be executed when the amount of free space on segname drops below free_space. 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.

Examples

Example 1 – Creates a threshold for segment1. When the free space on segment1 drops below 200 pages, the SAP ASE server executes the procedure pr_warning:
```
sp_addthreshold mydb, segment1, 200, pr_warning
```
Example 2 – Creates a threshold for the user_data segment. When the free space on user_data falls below 100 pages, the SAP ASE server executes a remote procedure call to the Open Server mail_me procedure:
```
sp_addthreshold userdb, user_data, 100, "o_server...mail_me"
```
Example 3 – Creates a threshold on the indexes segment of the pubs2 database. You can issue this command from any database:
```
pubs2..sp_addthreshold pubs2, indexes, 100, pr_warning
```

Usage

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:

If the procedure name does not specify a database, the SAP ASE 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_”, the SAP ASE server looks in the sybsystemprocs database.

If the procedure is not found in either database, the SAP ASE server sends an error message to the error log.

The SAP ASE 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.

Permissions

The permission checks for sp_addthreshold 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.

Auditing

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

Creating Additional Thresholds
Each database can have up to 256 thresholds, including the last-chance threshold.
Executing Threshold Procedures
Tasks 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.”
Changing or Deleting Thresholds
To change or delete thresholds, use sp_helpthreshold, sp_modifythreshold, and sp_dropthreshold.
Disabling Free-Space Accounting
Use the no free space acctg option of sp_dboption to disable free-space accounting on non-log segments.
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, called the last-chance threshold, is calculated by the SAP ASE server and cannot be changed by users.
Creating Threshold Procedures
Any user with create procedure privilege can create a threshold procedure in a database. Usually, a system administrator creates sp_thresholdaction in the sybsystemprocs database, and the database owners create threshold procedures in user databases.

Parent topic: System Procedures

Related reference