sp_metrics  sp_modify_time_range

Chapter 1: System Procedures

sp_modify_resource_limit

Description

Changes a resource limit by specifying a new limit value, or the action to take when the limit is exceeded, or both.

Syntax

sp_modify_resource_limit {name, appname } 
	rangename , limittype , limitvalue , enforced , action , scope

Parameters

name

is the Adaptive Server login to which the limit applies. You must specify either a name or an appname or both. To modify a limit that applies to all users of a particular application, specify a name of null.

appname

is the name of the application to which the limit applies. You must specify either a name or an appname or both. If the limit applies to all applications used by name, specify an appname of null. If the limit governs 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. You cannot modify this value, but you must specify a non-null value to uniquely identify the resource limit.

limittype

is the type of resource to which the limit applies. You cannot modify this value, but you must specify a non-null value to uniquely identify the resource limit. The value must be one of the following:

Limit type

Description

row_count

Limits the number of rows a query can return

elapsed_time

Limits the number of seconds in wall-clock time that a query batch or transaction can run

io_cost

Limits either the actual cost, or the optimizer’s cost estimate, for processing a query

tempdb_space

Limits the number of pages from a tempdb database that a single session can have

limit_value

is the maximum amount of the server resource that the login or application can use before Adaptive Server enforces the limit. This must be a positive integer less than or equal to 231 or null to retain the existing value. The following table indicates what value to specify for each limit type:

Limit type

Limit value

row_count

The maximum number of rows a query can return before the limit is enforced

elapsed_time

The maximum number of seconds in wall-clock time that a query batch or transaction can run before the limit is enforced

io_cost

A unitless measure derived from optimizer’s costing formula

tempdb_space

Limits the number of pages from a temporary database that a single session can have.

enforced

determines whether the limit is enforced prior to or during query execution. You cannot modify this value. Use null as a placeholder.

action

is the action to take when the limit is exceeded. The following codes apply to all limit types:

Action code

Description

1

Issues a warning

2

Aborts the query batch

3

Aborts the transaction

4

Kills the session

null

Retains the existing value

scope

is the scope of the limit. You cannot modify this value. You can use null as a placeholder.

Examples

Example 1

Modifies a resource limit that applies to all applications used by “robin” during the weekends time range. The limit issues a warning when a query is expected to return more than 3000 rows:

sp_modify_resource_limit robin, NULL, weekends, row_count, 3000, NULL, 
    1, NULL

Example 2

Modifies a resource limit that applies to the acctg application on all days of the week and at all times of the day. The limit aborts the query batch when estimated query processing time exceeds 45 seconds:

sp_modify_resource_limit NULL, acctg, "at all times", elapsed_time, 
    45, 2, 2, 6

Usage

Permissions

Only a System Administrator can execute sp_modify_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 For more information, see the System Administration Guide.

System procedures sp_add_resource_limit, sp_drop_resource_limit, sp_help_resource_limit





Copyright © 2005. Sybase Inc. All rights reserved. sp_modify_time_range

View this book as PDF