Snowflake Warehouse Auto-Suspend Interruption

Question

Snowflake Warehouse Auto-Suspend Interruption

LD Lissandra 40

Hello everyone,

An analytical Snowflake virtual warehouse remains active continuously even during periods when no queries appear to be actively running, resulting in unexpectedly high credit consumption. We have AUTO_SUSPEND configured, but the warehouse does not suspend as expected, suggesting that there may be long-running or uncommitted transactions, background activity, or other sessions keeping the warehouse active.

How can we identify long-running or uncommitted transactions and determine which sessions, queries, or users are preventing the warehouse from becoming idle and triggering AUTO_SUSPEND?

In particular, which ACCOUNT_USAGE / INFORMATION_SCHEMA views or Snowflake commands should we use to correlate active sessions, transaction state, query history, and warehouse activity? We would also like to distinguish between genuinely active workloads and sessions that are simply holding an open transaction without executing queries.

What would be the recommended troubleshooting approach to confirm the root cause before changing the warehouse's AUTO_SUSPEND configuration?

Thanks.

0 comments

1 answer

Your answer

Answer 1

Hello Lissandra,

Thank you for posting question on Microsoft Windows Forum!

Based on the issue description. When a Snowflake virtual warehouse fails to auto-suspend despite having an active AUTO_SUSPEND policy and zero visible queries running, the possible causes might be an open, uncommitted transactions or lingering active sessions holding the warehouse lock. Snowflake requires both a complete absence of executing queries and zero open transactions before the auto-suspend timer can initiate or complete.

The suggested approach here is to check warehouse state and settings by running DESCRIBE WAREHOUSE <warehouse_name> to verify that AUTO_SUSPEND is set to an appropriate non-zero value (e.g., 300 or 600 seconds) and that AUTO_RESUME is enabled. Another suggestion is to inspect open transactions. Execute SHOW TRANSACTIONS IN ACCOUNT; and look for rows where the started_on time is much older than your auto-suspend window. Note the id and session values.

It is worth correlating with query & session history. Query SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY filtered by the offending session_id to inspect the last statement executed before the session went silent. This usually uncovers the misbehaving application, script, or JDBC/ODBC connector.

In case, a rogue transaction is verified, you can abort it directly using the system function or alternatively closing the underlying session if necessary. Once the uncommitted transaction is rolled back or aborted and active queries finish, monitor the warehouse. It should transition to a Suspended state within the configured AUTO_SUSPEND timeframe.

If this behavior happens repeatedly from the same client tool or application, investigate the client-side code to ensure database sessions properly issue explicit COMMIT or ROLLBACK statements, or verify that driver autocommit settings are correctly configured.

I hope you have found something useful here. If it helps you get more insight into the issue, it is appreciated to accept the answer. Should you have more questions, feel free to leave a message. Have a nice day!