sp_droprowlockpromote

Syntax

The syntax for dropping server-wide partition lock promotion settings is:

sp_droprowlockpromote_ptn "server"

The syntax for dropping the partition lock promotion threshold at the database or table level is:

sp_droprowlockpromote_ptn {"database" | "table"}, objname

server – removes server-wide values for the partition lock promotion thresholds.
"database" | "table" – specifies whether to remove the partition lock promotion thresholds for a database or table. These are Transact-SQL keywords and therefore, require quotes.
objname – is the name of the table or database from which to remove the partition lock promotion thresholds.

Example 1 – Removes the partition lock promotion values from the sales table. Partition lock promotion for sales now uses the database or server-wide values:
```
sp_droprowlockpromote_ptn "table", "sales"
```

There are additional considerations when using sp_droprowlockpromote_ptn:

Use sp_droprowlockpromote_ptn to drop partition lock promotion values set with sp_setrowlockpromote_ptn.
When you drop a database’s partition lock promotion thresholds, datarows-locked tables that do not have partition lock promotion thresholds configured at table level use the server-wide values. Use sp_configure to check the value of the partition lock promotion configuration parameters.
When a table’s partition lock promotion values are dropped, the SAP ASE server uses the database’s partition lock promotion thresholds, if they are configured, or the server-wide values, if no thresholds are set for the database.
To change the partition lock promotion thresholds for a database, you must be using the master database. To change the partition lock promotion thresholds for a table in a database, you must be using the database where the table resides.
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.

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

Setting	Description
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.

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

Information	Values
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