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.

If 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

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

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

sp_help_resource_limit reports on all resource limits, limits for a given login or application, limits in effect at a given time or day of the week, or limits with a given scope or action.

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