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?
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
Or you could also choose to check the warehouse credits from the Account -> Usage tab in the Snowflake Web UI.
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
;