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

NoteIf 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

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

See also

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

System procedures sp_add_resource_limit, sp_droplogin, sp_help_resource_limit, sp_modify_resource_limit