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.


Example 1

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

Example 2

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"

Example 3

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


Crossing a threshold

The last-chance threshold

Creating additional thresholds

Creating threshold procedures

Executing threshold procedures

Changing or deleting thresholds

Disabling free-space accounting

WARNING! System procedures cannot provide accurate information about space allocation when free-space accounting is disabled.


Only the Database Owner or a System Administrator can execute sp_addthreshold.

See also

Commands create procedure, dump transaction

Functions lct_admin

System procedures sp_dboption, sp_dropthreshold, sp_helpthreshold, sp_modifythreshold, sp_thresholdaction