When you use sp_help_resource_limit without any parameters, Adaptive Server lists all resource limits within the server. For example:
sp_help_resource_limit
name appname rangename rangeid limitid limitvalue enforced action scope ---- ------- --------- ------- ------- ---------- -------- ------ ----- NULL acctng evenings 4 2 120 2 1 2 stein NULL weekends 1 3 5000 2 1 1 joe_user acctng bus_hours 5 3 2500 2 2 1 joe_user finance bus_hours 5 2 160 2 1 6 wong NULL mornings 2 3 2000 2 1 1 wong acctng bus_hours 5 1 75 1 3 1
The rangeid column prints the value from systimeranges.id that corresponds to the name in the rangename column. The limitvalue column reports the value set by sp_add_resource_limit or sp_modify_resource_limit. Table 1-2 shows the meaning of the values in the limitid, enforced, action, and scope columns.
Column |
Meaning |
Value |
|---|---|---|
limitid |
What kind of limit is it? |
1– I/O cost 2 – elapsed time 3 – row count |
enforced |
When is the limit enforced? |
1 – before execution 2 – during execution 3 – both |
action |
What action is taken when the limit is hit? |
1– issue a warning 2 – abort the query batch 3 – abort the transaction 4 – kill the session |
scope |
What is the scope of the limit? |
1 – query 2 – query batch 4 – transaction 6 – query batch + transaction |
If a system administrator specifies a login name when executing sp_help_resource_limit, Adaptive Server lists all resource limits for that login. The output displays not only resource limits specific to the named user, but all resource limits that pertain to all users of specified applications, because the named user is included among all users.
For example, the following output shows all resource limits that apply to “joe_user.” Because a resource limit is defined for all users of the acctng application, this limit is included in the output.
sp_help_resource_limit joe_user
name appname rangename rangeid limitid limitvalue enforced action scope ---- ------- --------- ------- ------- ---------- -------- ------ ----- NULL acctng evenings 4 2 120 2 1 2 joe_user acctng bus_hours 5 3 2500 2 2 1 joe_user finance bus_hours 5 2 160 2 1 6
