Example of listing all existing resource limits

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

In the output, 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 6-2 shows the meaning of the values in the limitid, enforced, action, and scope columns.

Table 6-2: Values for sp_help_resource_limit output

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