Reports on resource limits.
sp_help_resource_limit [name [, appname [, limittime [, limitday [, scope [, action[, verbose]]]]]]]
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.
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.
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.
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.
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 |
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 |
when used, the output is displayed in the verbose mode, with value 1 or 0 (zero).
Lists all resource limits stored in the sysresourcelimits system table:
sp_help_resource_limit
Lists all limits for the user “joe_user”:
sp_help_resource_limit joe_user
Lists all limits for the application my_app:
sp_help_resource_limit NULL, my_app
Lists all limits enforced at 9:00 a.m.:
sp_help_resource_limit NULL, NULL, "09:00"
An alternative way of listing the limits enforced at 9:00 a.m.:
sp_help_resource_limit @limittype = "09:00"
Lists all limits enforced on Mondays:
sp_help_resource_limit NULL, NULL, NULL, Monday
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
To list all limits in verbose mode:
sp_help_resource_limit null,null,null,null,null,null,1
To list all resource limits in verbose mode:
sp_help_resource_limit @verbose=1
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.
Any user can execute sp_help_resource_limit to list his or her own resource limits. Only a system administrator can execute sp_help_resource_limit to list limits that apply to other users.
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_drop_resource_limit, sp_modify_resource_limit