Chapter 1: System Procedures

sp_drop_resource_limit

Description

Removes one or more resource limits from Adaptive Server.

Syntax

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

Parameters

name: is the Adaptive Server 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 Adaptive 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 following table lists the valid values for each limit type:

Enforced code	Description	Limit type
1	Drops only limits for which action is taken when the estimated cost of execution exceeds the specified limit.	io_cost
2	Drops only limits for which action is taken when the actual row count, elapsed time, or cost of execution exceeds the specified limit.	row_count elapsed_time io_cost
3	Drops only limits for which action is taken when either the estimated cost (1) or the actual cost (2) exceeds the specified limit.	io_cost
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. This must be one of the following:

Action code	Description
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. This must be one of the following:

Scope code	Description
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

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 whose action is to kill the session:

sp_drop_resource_limit NULL, payroll, NULL, NULL, NULL, 4, NULL

Usage

Use the sp_help_resource_limit system procedure to determine which resource limits apply to a given user, application, or time of day.
When you use sp_droplogin to drop an Adaptive Server 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.

Permissions

Only a System Administrator can execute sp_drop_resource_limit.

Auditing

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

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