Controlling how often sp_thresholdaction executes

Adaptive Server uses a hysteresis value, the global variable @@thresh_hysteresis, to control how sensitive thresholds are to variations in free space.

A threshold is deactivated after it executes its procedure, and remains inactive until the amount of free space in the segment rises @@thresh_hysteresis pages above the threshold. This prevents thresholds from executing their procedures repeatedly in response to minor fluctuations in free space. You cannot change the value of @@thresh_hysteresis.

For example, when the threshold in Figure 16-2 executes sp_thresholdaction, it is deactivated. In Figure 16-3, the threshold is reactivated when the amount of free space increases by the value of @@thresh_hysteresis:

Figure 16-3: Free space must rise by @@thresh_hysteresis to reactivate threshold

Graphic showing the layout of the log segment and the placement of the last-chance threshold.in the middle of the free space and the placement of @@thresh_hysteresis.