Setting thresholds includes certain restrictions and limitations.
When threshold procedures are installed on multiple segments in one or more databases, the expansion is performed in the order in which the thresholds fire. If abort tran on log full is off for the logsegment, tasks wait until the threshold procedure for the logsegment is scheduled to alter the database.
In unlogged segments, tasks continue to process even after the free space threshold is crossed, while the threshold procedure remains in queue. This can cause “out of space” errors in data segments. Design your thresholds to have sufficient space in the database for the task to complete.
If many threshold procedures fire simultaneously, the procedure cache may become overloaded. This is more likely to occur in installations with large numbers of databases, many segments, and many threshold action procedures installed.
If the space in the tempdb is very low, and other operations need tempdb resources, the threshold procedures may fail even while trying to correct the situation. Make sure that threshold procedures in tempdb are installed with sufficiently large amounts of free space, at least 2MB, to avoid this problem.
You may need to change your dump and load procedures to manage site-specific policies that determine how databases and devices expand.
Dumping a database does not transport information stored in master.dbo.sysattributes, so if you use dump and load to migrate databases from a source server to a target server, you must manually migrate any site-specific policies encoded as data in the sysattributes database. There are two possible workarounds:
Using bcp out from a view defined on master.dbo.sysattributes for entries with class number 19, you can manually extract the data from master.dbo.sysattributes, then use bcp in to load the data into the target server. This requires that both databases across the two servers have the same segment IDs.
You can also use the ddlgen feature of SAP Command Center to regenerate the sp_dbextend set invocations necessary to re-create your policy rules, by running the ddlgen script at the target server. However, you cannot use ddlgen to manage renamed logical devices across servers procedure. You must manually rename the devices at the target server.
These restrictions do not cause failure:
You can install a threshold action on an unlogged segment when the database has sp_dboption ‘no free space acctg’ enabled (see the Reference Manual: Procedures). This option means only that no database expansion is performed, since threshold actions are not fired with this option is off. Leaving this option on generates a warning message.
SAP also recommends that you periodically dump the master database if expansion occurs, so that you can re-create the master database in case of failure after several expansions.
SAP recommends that you do not install these generic threshold procedures on any system databases, particularly the master database, as modifying space usage in the master database requires special treatment (see the Reference Manual: Commands.)
You cannot use thresholds to shrink a database or segment.