Creates a threshold to monitor space on a database segment. When free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure.
sp_addthreshold dbname, segname, free_space, proc_name
is the database for which to add 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 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 Adaptive Server or on an Open Server. Thresholds cannot execute procedures on remote Adaptive Servers.
Creates a threshold for segment1. When the free space on segment1 drops below 200 pages, Adaptive Server executes the procedure pr_warning:
sp_addthreshold mydb, segment1, 200, pr_warning
Creates a threshold for the user_data segment. When the free space on user_data falls below 100 pages, Adaptive Server executes a remote procedure call to the Open Server mail_me procedure:
sp_addthreshold userdb, user_data, 100, "o_server...mail_me"
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
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, called 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. sp_dboption "abort tran on log full", true causes Adaptive Server to roll back all transactions that have not yet been logged when the last-chance threshold is crossed.
All databases have a last-chance threshold, including master. The threshold is an estimate of the number of free log pages that are required to back up the transaction log. As you allocate more space to the log segment, Adaptive Server automatically adjusts the last-chance threshold.
Each database can have up to 256 thresholds, including the last-chance threshold.
When you add a threshold, it must be at least 2 times @@thresh_hysteresis pages from the closest threshold.
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.
sp_addthreshold does not verify that the specified procedure exists. It is possible to add a threshold before creating the procedure it executes.
sp_addthreshold checks to ensure that the user adding the threshold procedure has been directly granted the “sa_role”. All system roles active when the threshold procedure is created 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
@segmentname, 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 it 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, you can create a single threshold procedure in the sybsystemprocs database that is executed for all thresholds in Adaptive Server.
Include print and raiserror statements in the threshold procedure to send output to the error log.
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”.
Adaptive Server executes the threshold procedure with the permissions the user had at the time he or she added the threshold, minus any permissions that have since been revoked.
Each threshold procedure uses one user connection, for as long as it takes for the procedure to execute.
Use sp_helpthreshold for information about existing thresholds.
Use sp_modifythreshold to associate a threshold with a new threshold procedure, free-space value, or segment. (You cannot change the free-space value or segment name associated with the last-chance threshold.)
Each time a user modifies a threshold, that user becomes the threshold owner. When the threshold is crossed, Adaptive Server executes the threshold with the permissions the owner had at the time he or she modified the threshold, minus any permissions that have since been revoked.
Use sp_dropthreshold to drop a threshold from a segment.
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.
The permission checks for sp_addthreshold 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 sso_role. |
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 |
|
Documents See the System Administration Guide for more information about using thresholds.
Commands create procedure, dump transaction
Functions lct_admin
System procedures sp_dboption, sp_dropthreshold, sp_helpthreshold, sp_modifythreshold, sp_thresholdaction
