sp_ dropglockpromote_ptn

Removes partition lock promotion values.

Syntax

The syntax for dropping server-wide partition lock promotion settings is:
sp_dropglockpromote_ptn "server"
The syntax for dropping the partition lock promotion threshold at the database or table level is:
sp_dropglockpromote_ptn {"database" | "table"}, objname   

Parameters

Examples

Usage

There are additional considerations when using sp_dropglockpromote_ptn:
  • Use sp_dropglockpromote_ptn to drop partition lock promotion values set with sp_setpglockpromote_ptn.

  • When you drop a database’s partition lock promotion thresholds, tables that do not have partition lock promotion thresholds configured use the server-wide values.

  • When a table’s values are dropped, the SAP ASE server uses the database’s lock promotion thresholds if they are configured or the server-wide values if they are not.

  • When you drop server-wide partition lock promotion thresholds, partition lock promotion threshold values set at the table level will be used. Otherwise, partition lock promotion threshold values set at the database level will be used. If partition lock promotion threshold values are not set at either database or table level, then partition lock promotion is disabled. It can be enabled again using sp_setrowlockpromote_ptn.

Permissions

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

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage lock promotion threshold privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related concepts
Dropping Partition Lock Promotion Thresholds
Related reference
sp_droprowlockpromote_ptn