sp_addthreshold

Description

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.

Syntax

sp_addthreshold dbname, segname, free_space, proc_name

Parameters

dbname

is the database for which to add the threshold. This must be the name of the current database.

segname

is the segment for which to monitor free space. Use quotes when specifying the “default” segment.

free_space

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.

proc_name

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.

Examples

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

Usage


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.

Permissions

The permission checks for sp_addthreshold differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage database privilege.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sso_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Documents See the System Administration Guide for more information about using thresholds.

Commands create procedure, dump transaction

Functions lct_admin

System procedures sp_dboption, sp_dropthreshold, sp_helpthreshold, sp_modifythreshold, sp_thresholdaction