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.
@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.