Removes one or more resource limits from Adaptive Server.
sp_drop_resource_limit { name, appname }
[, rangename, limittype, enforced, action, scope]
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.
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.
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.
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.
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. |
is the action taken when the limit is exceeded, and must be one of these:
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. |
is the scope of the limit, and 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. |
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.
Drops all limits that apply to joe’s use of the payroll application:
sp_drop_resource_limit joe, payroll
Drops all limits that apply to the user “joe”:
sp_drop_resource_limit joe
Drops all resource limits that apply to the payroll application:
sp_drop_resource_limit NULL, payroll
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
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.
Only a system administrator can execute sp_drop_resource_limit.
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 |
|
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
