Categories:

Warehouse & Resource Monitor DDL

# ALTER RESOURCE MONITOR¶

Modifies the properties and triggers for an existing resource monitor. Use this command to increase or decrease the credit quota, change the scheduling information, or change/replace the triggers for a resource monitor.

## Syntax¶

ALTER RESOURCE MONITOR [ IF EXISTS ] <name> [ SET { [ CREDIT_QUOTA = <num> ]
[ FREQUENCY = { MONTHLY | DAILY | WEEKLY | YEARLY | NEVER } ]
[ START_TIMESTAMP = { <timestamp> | IMMEDIATELY } ]
[ END_TIMESTAMP = <timestamp> ] } ]
[ TRIGGERS triggerDefinition [ triggerDefinition ... ] ]


Where:

triggerDefinition ::=
ON <threshold> PERCENT DO { SUSPEND | SUSPEND_IMMEDIATE | NOTIFY }


## Parameters¶

name

Specifies the identifier for the resource monitor to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

SET ...

CREDIT_QUOTA = num

Specifies the number of credits allocated to the resource monitor per frequency interval. When total usage for all warehouses assigned to the monitor reaches this number for the current frequency interval, the resource monitor is considered to be at 100% of quota.

If a value is not specified for a resource monitor, the monitor has no quota and will never reach 100% usage within the specified interval.

FREQUENCY = MONTHLY | DAILY | WEEKLY | YEARLY | NEVER

The frequency interval at which the credit usage resets to 0.

If you specify NEVER for the frequency, the credit usage for the warehouse does not reset.

START_TIMESTAMP = timestamp | IMMEDIATELY

The date and time when the resource monitor starts monitoring credit usage for the assigned warehouses.

If you specify IMMEDIATELY for the start timestamp, the current timestamp is used.

END_TIMESTAMP = timestamp

The date and time when the resource monitor suspends the assigned warehouses.

TRIGGERS ... (aka actions)

Specifies one or more triggers for the resource monitor. Each trigger definition consists of:

• ON threshold PERCENT (usage percentage; values larger than 100 are supported)

• DO SUSPEND | SUSPEND_IMMEDIATE | NOTIFY (action to perform when the threshold is reached).

For more details, see CREATE RESOURCE MONITOR.

## Usage Notes¶

• If a SUSPEND or SUSPEND_IMMEDIATE trigger is active for a resource monitor and the trigger threshold has been reached for the specified frequency interval, thereby preventing all assigned warehouses from being started/resumed, you can use this command to either increase the credit quota above the trigger threshold or replace the trigger with a new trigger with a higher threshold.

Once the credit quota or trigger threshold for the resource monitor has been increased, assigned warehouses can be started or resumed.

• The TRIGGERS parameter is not additive (i.e. it removes all existing triggers for the resource monitor and replaces them with the specified triggers).

As a result, to make additions to the existing triggers, you must specify the new triggers and replicate the existing triggers.

## Examples¶

Specify a new credit quota for the limiter resource monitor and replace the existing triggers for the monitor with a new set of triggers:

ALTER RESOURCE MONITOR limiter
SET CREDIT_QUOTA=2000
TRIGGERS ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND_IMMEDIATE;