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.

sp_modifythreshold does not verify that the specified procedure exists. It is possible to associate a threshold with a procedure that does not yet exist.

sp_modifythreshold checks to ensure that the user modifying the threshold procedure has been granted the “sa_role”. All system roles active when the threshold procedure is created are modified in systhresholds as valid roles for the user writing the procedure.

The SAP ASE server passes four parameters to a threshold procedure:
  • @dbname, varchar(30), which identifies the database

  • @segment_name, 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 the procedure 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, create a single threshold procedure in the sybsystemprocs database that can be executed by all thresholds.

Include print and raiserror statements in the threshold procedure to send output to the error log.