Creating Threshold Procedures

SAP does not supply threshold procedures; create them yourself to ensure that they are tailored to your site’s needs.

Suggested actions for a threshold procedure include writing to the server’s error log and dumping the transaction log to increase the amount of log space. You can also execute remote procedure calls to an Open Server or to XP Server.

For example, if you include the following command in sp_thresholdaction, it executes a procedure named mail_me on an Open Server:

exec openserv...mail_me @dbname, @segment

See the Transact-SQL Users Guide.

This section provides some guidelines for writing threshold procedures, as well as two sample procedures.

Parameters for Threshold Procedures
SAP ASE passes four parameters to a threshold procedure.
Generating Error Log Messages
Include a print statement near the beginning of your procedure to record the database name, segment name, and threshold size in the error log.
sp_thresholdaction Procedures that Include a dump transaction
If your sp_thresholdaction procedure includes a dump transaction command, SAP ASE dumps the log to the devices named in the procedure.
A Simple Threshold Procedure
Youc an create a simple procedure that dumps the transaction log and prints a message to the error log.
A More Complex Procedure
You can create a completx threshold procedure that performs different actions, depending on the value of the parameters passed to it.
Placement for Threshold Procedures
Although you can create a separate procedure to dump the transaction log for each threshold, it is easier to create a single threshold procedure that is executed by all log segment thresholds.

Parent topic: Managing Free Space with Thresholds

Creating Additional Thresholds on Data and Log Segments