Changing or Specifying a New Free-Space Threshold

Use sp_modifythreshold to associate a free-space threshold with a new threshold procedure, free-space value, segment, or change the name of the procedure associated with the last-chance threshold.

sp_modifythreshold drops the existing threshold and creates a new one in its place. See the Reference Manual: Procedures

For example, to execute a threshold procedure when free space on the segment falls below 175 pages rather than below 200 pages, enter:
sp_modifythreshold mydb, "default", 200, NULL, 175

In this example, NULL acts as a placeholder so that new_free_space falls in the correct place in the parameter list. The name of the threshold procedure does not change.

sp_modifythreshold requires that you specify the number of free pages associated with the last-chance threshold. Use sp_helpthreshold to determine this value

The person who modifies the threshold becomes the new threshold owner. When the amount of free space on the segment falls below the threshold, SAP ASE executes the threshold procedure with the owner’s permissions at the time he or she executed sp_modifythreshold, less any permissions that have since been revoked.

This example displays information about the last-chance threshold, and then specifies a new procedure, sp_new_thresh_proc, to execute when the threshold is crossed:

sp_helpthreshold logsegment
segment name   free pages   last chance?   threshold procedure
------------   ----------   ------------   -------------------
logsegment     40                      1   sp_thresholdaction

(1 row affected, return status = 0)
sp_modifythreshold mydb, logsegment, 40, sp_new_thresh_proc