sp_drop_resource

Syntax

sp_drop_resource_limit { name, appname }
	[, rangename, limittype, enforced, action, scope]

Parameters

name – is the SAP ASE login to which the limit applies. To drop resource limits that apply to all users of a particular application, specify the appname and a name of NULL.
appname – is the application to which the limit applies. To drop resource limits that apply to all applications used by the specified login, specify the login name and an appname of NULL. To drop a limit that applies to a particular application, specify the application name that the client program passes to the SAP ASE server in the login packet.
rangename – is the time range during which the limit is enforced. This must be an existing time range stored in the systimeranges system table or NULL to delete all resource limits for the specified name, appname, limittype, action, and scope, without regard to rangename.
limittype – is the type of resource being limited. This must be one of the following:
- row_count – drops only limits that restrict the number of rows a query can return.
- elapsed_time – drops only limits that restrict the number of seconds that a query batch or transaction can run.
- io_cost – drops only limits that restrict actual or estimated query processing cost.
- tempdb_space – drops only the limits of the number of tempdb database pages that a single session used or can have.
- NULL – drops all resource limits with the specified name, appname, rangename, enforcement time, action, and scope, without regard to limittype.
enforced – determines whether the limit is enforced prior to or during query execution. The valid values for each limit type are:
- 1 – drops only limits for which action is taken when the estimated cost of execution exceeds the specified limit.
- 2 – drops only limits for which action is taken when the actual row count, elapsed time, or cost of execution exceeds the specified limit.
- 3 – drops only limits for which action is taken when either the estimated cost (1) or the actual cost (2) exceeds the specified limit.
- NULL – drops all resource limits with the specified name, appname,rangename, limittype, and scope, without regard to when the action is enforced.
action – is the action taken when the limit is exceeded, and must be one of these:
- 1 – drops only limits that issue a warning.
- 2 – drops only limits that abort the query batch.
- 3 – drops only limits that abort the transaction.
- 4 – drops only limits that kill the session.
- NULL – drops all resource limits with the specified name, appname,rangename, limittype, enforcement time, and scope, without regard to the action they take.
scope – is the scope of the limit, and must be one of the following:
- 1 – drops only limits that apply to queries.
- 2 – drops only limits that apply to query batches.
- 4 – drops only limits that apply to transactions.
- 6 – drops only limits that apply to both query batches and transactions.
- NULL – drops all resource limits with the specified name, appname,rangename, limittype, enforcement time, and action, without regard to their scope.

Examples

Example 1 – Drops the single resource limit that kills the session whenever joe’s use of the payroll application runs a query during the friday_afternoon time range that results in excessive execution-time I/O cost:
```
sp_drop_resource_limit joe, payroll, friday_afternoon, io_cost, 2, 4, 1
```
Note: If no resource limit matches these selection criteria, sp_drop_resource_limit returns without error.
Example 2 – Drops all limits that apply to joe’s use of the payroll application:
```
sp_drop_resource_limit joe, payroll
```
Example 3 – Drops all limits that apply to the user “joe”:
```
sp_drop_resource_limit joe
```
Example 4 – Drops all resource limits that apply to the payroll application:
```
sp_drop_resource_limit NULL, payroll
```
Example 5 – Drops all resource limits on the payroll application with an action that is to kill the session:
```
sp_drop_resource_limit NULL, payroll, NULL, NULL, NULL, 4, NULL
```

Usage

To determine which resource limits apply to a given user, application, or time of day, use sp_help_resource_limit.

When you use drop login to drop an SAP ASE login, all resource limits associated with that login are also dropped.

The deletion of a resource limit causes the limits for each session for that login and/or application to be rebound at the beginning of the next query batch for that session.

See the System Administration Guide for more information on resource limits.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage resource limit` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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