sp_help_resource_limit

Description

Reports on resource limits.

Syntax

sp_help_resource_limit [name [, appname [, limittime
	[, limitday [, scope [, action[, verbose]]]]]]]

Parameters

name

is the Adaptive Server login to which the limits apply. For information about limits that govern a particular login, specify the login name. For information about limits without regard to login, specify null.

NoteIf you are not a system administrator, specify your own login, or a login of NULL, to display information about the resource limits that apply to you.

appname

is the name of the application to which the limit applies. For information about limits that govern a particular application, specify the application name that the client program passes to the Adaptive Server in the login packet. For information about limits without regard to application, specify null.

limittime

is the time during which the limit is enforced. For information about limits in effect at a given time, specify the time, with a value between “00:00” and “23:59”, using the following form:

"HH:MM"

For information about limits without regard to time, specify null.

limitday

is any day on which the limit is enforced. For information about resource limits in effect on a given day of the week, specify the full weekday name for the default server language, as stored in the syslanguages system table of the master database. For information about limits without regard to the days on which they are enforced, specify null.

scope

is the scope of the limit. Specify one of the following:

Scope code

For help on all limits that govern

1

Queries

2

Query batches (one or more SQL statements sent by the client to the server)

4

Transactions

6

Both query batches and transactions

NULL

The specified name, appname, limittime, limitday, and action, without regard to their scope

action

is the action to take when the limit is exceeded. Specify one of the following:

Action code

For help on all limits that

1

Issue a warning

2

Abort the query batch

3

Abort the transaction

4

Kill the session

NULL

Govern the specified name, appname, limittime, limitday, and scope, without regard to the action they take

verbose

when used, the output is displayed in the verbose mode, with value 1 or 0 (zero).

Examples

Example 1

Lists all resource limits stored in the sysresourcelimits system table:

sp_help_resource_limit

Example 2

Lists all limits for the user “joe_user”:

sp_help_resource_limit joe_user

Example 3

Lists all limits for the application my_app:

sp_help_resource_limit NULL, my_app

Example 4

Lists all limits enforced at 9:00 a.m.:

sp_help_resource_limit NULL, NULL, "09:00"

Example 5

An alternative way of listing the limits enforced at 9:00 a.m.:

sp_help_resource_limit @limittype = "09:00"

Example 6

Lists all limits enforced on Mondays:

sp_help_resource_limit NULL, NULL, NULL, Monday

Example 7

Lists any limit in effect for “joe_user” on Mondays at 9:00 a.m.

sp_help_resource_limit joe_user, NULL, "09:00", Monday

Example 8

To list all limits in verbose mode:

sp_help_resource_limit null,null,null,null,null,null,1

Example 9

To list all resource limits in verbose mode:

sp_help_resource_limit @verbose=1

Usage

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be a user with manage resource limit privilege. Any user can execute sp_help_resource_limit to list their own resource limits.

Granular permissions disabled

With granular permissions disabled, you must be a user with sa_role. Any user can execute sp_help_resource_limit to list their own resource limits.

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_drop_resource_limit, sp_modify_resource_limit