CODEWITHSUNDEEP
snowflake-cloud-data-platform
Mark McGown
Mark McGown

Reputation: 1115

Snowflake Warehouse Cannot Be Suspended?

I have a Snowflake Warehouse using 10 credits so far this month I no longer believe to be in use. To safely verify it's no longer in use I tried to:

ALTER WAREHOUSE MY_WAREHOUSE SUSPEND

But I received the message:

Invalid state. Warehouse 'MY_WAREHOUSE' cannot be suspended.

Why might the warehouse not be able to be suspended? What else do I need to check and run?

Additionally, is there a way to check if any queries/loaders have utilized this warehouse in the past X days?

Upvotes: 3

Views: 2607

Answers (2)

sprethepa
sprethepa

Reputation: 797

While issuing the ALTER WAREHOUSE ... SUSPEND command, you will receive the error "Invalid state. Warehouse 'MY_WAREHOUSE' cannot be suspended" when the warehouse has already been suspended.

Before issuing the SUSPEND command, check the status of the warehouse using the below command

show warehouses like '%TEST%' in account;

The STATE column of the resultset will give the status of the warehouse.

To check if there were any queries that were using this warehouse, you can use the below query

select * from snowflake.account_usage.query_history where warehouse_name='Warehouse name' and START_TIME > 'Timestamp';

https://docs.snowflake.com/en/sql-reference/account-usage/query_history.html#query-history-view

https://docs.snowflake.com/en/sql-reference/functions/query_history.html#query-history-query-history-by

Or you could also choose to check the warehouse credits from the Account -> Usage tab in the Snowflake Web UI.

Upvotes: 4

Adrian White
Adrian White

Reputation: 1804

try dropping the auto suspend e.g. running something like this :

CREATE OR REPLACE WAREHOUSE SOME_FANCY_WAREHOUSE_NAME
WITH WAREHOUSE_SIZE = 'MEDIUM'
MAX_CLUSTER_COUNT = 1
MIN_CLUSTER_COUNT = 1
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE

;

Upvotes: 1

Related Questions