Stop a runaway Snowflake query: cancel by query ID, session, user or warehouse, then set timeouts

#Symptom

A warehouse is burning credits with no end in sight, queries are queuing behind one long statement, or someone says "I closed my laptop but the query is still running". You need it stopped now, without killing unrelated work.

Warning

ALTER WAREHOUSE ... SUSPEND will not get you out of this. Suspending does not abort running queries. The warehouse finishes them first, then shuts down.

#Step 1: find what is running

Use the INFORMATION_SCHEMA table function, because it includes statements still in progress. Don't use ACCOUNT_USAGE.QUERY_HISTORY here, because that view has latency.

USE DATABASE analytics;  -- any database; the function needs one in context

SELECT query_id,
       user_name,
       role_name,
       session_id,
       execution_status,
       ROUND(total_elapsed_time / 60000, 1) AS elapsed_min,
       query_tag,
       LEFT(query_text, 150) AS query_start
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
       WAREHOUSE_NAME => 'BI_WH',
       RESULT_LIMIT   => 10000))
WHERE UPPER(execution_status) IN ('RUNNING', 'QUEUED', 'BLOCKED', 'RESUMING_WAREHOUSE')
ORDER BY total_elapsed_time DESC;

Three details matter:

• Result limit. RESULT_LIMIT defaults to 100 and is applied before your WHERE clause. On a busy warehouse the default can hide the query you want, so raise it. 10,000 is the maximum.
• End time. Don't pass END_TIME_RANGE_END => CURRENT_TIMESTAMP(). With that set, the function returns only completed queries.
• Visibility. You always see your own queries. To see other people's, your role needs MONITOR or OPERATE on the warehouse, or MONITOR EXECUTION on the account.

To search by person instead of warehouse, use QUERY_HISTORY_BY_USER(USER_NAME => 'SVC_ETL', RESULT_LIMIT => 10000).

query_type shows UNKNOWN while a statement runs, so read query_text instead.

#Step 2: decide before you kill

Caution

Check with the owner before you cancel a long MERGE, DELETE or COPY that is close to finishing. Cancelling throws away the work, and a rerun starts from zero. Cancelling also doesn't stop whatever launched the query: Airflow, dbt or a task can resubmit it. Pause the source first.

#Step 3: stop it, narrowest command first

Query IDs contain hyphens, so keep the single quotes. Session IDs are numbers and take no quotes.

#Privileges

You can always cancel your own queries. For someone else's, the function reference asks for OWNERSHIP on that user or OPERATE or OWNERSHIP on the warehouse. For a task's query, it asks for OPERATE or OWNERSHIP on the task.

ALTER WAREHOUSE ... ABORT ALL QUERIES needs OPERATE. Grant it to on-call in advance, because you won't want to request it during an incident:

GRANT MONITOR, OPERATE ON WAREHOUSE bi_wh TO ROLE oncall_ops;

#Aborting a user does not lock them out

After ALTER USER ... ABORT ALL QUERIES, the user can still log in and submit new queries. To stop a misbehaving service account for good, disable it too, after confirming what else uses it:

ALTER USER svc_etl SET DISABLED = TRUE;
ALTER USER svc_etl ABORT ALL QUERIES;

Warning

Disabling a service user stops every job that uses it. Re-enable it with ALTER USER svc_etl SET DISABLED = FALSE;.

#Sessions

The session abort page doesn't say it also cancels running queries. Run SYSTEM$CANCEL_ALL_QUERIES first, then SYSTEM$ABORT_SESSION, and confirm with Step 4.

#Task runs

Suspend the task first, or the next schedule starts the work again:

ALTER TASK analytics.etl.load_orders SUSPEND;
SELECT SYSTEM$CANCEL_QUERY('<query_id>');

#Blocked queries

A BLOCKED query is a victim. Find the holder:

SHOW LOCKS IN ACCOUNT;  -- IN ACCOUNT requires ACCOUNTADMIN

The HOLDING row on the same resource identifies the holder through its query_id and transaction columns. Contact the owner or cancel its running query. If the transaction has to go, the SHOW LOCKS documentation points to SYSTEM$ABORT_TRANSACTION. Check its reference page before you run it.

#Step 4: confirm it stopped

The message a cancel function returns doesn't prove the query has ended. Check the query itself:

SELECT query_id, execution_status, error_message, end_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
       WAREHOUSE_NAME => 'BI_WH',
       RESULT_LIMIT   => 10000))
WHERE query_id = '<query_id>';

There is no "cancelled" status. A cancelled query is identified by error_message containing SQL execution canceled. Then re-run the Step 1 query and watch for the same statement returning under a new query ID, which means a scheduler or retry loop is still active.

#Prevent it next time

#Set timeouts on the warehouse

The statement timeout defaults to two days (172,800 seconds), and the queued timeout defaults to 0 (no timeout). Check what is actually in force:

SHOW PARAMETERS LIKE 'STATEMENT%' IN WAREHOUSE bi_wh;
SHOW PARAMETERS LIKE 'STATEMENT%' IN USER svc_etl;
SHOW PARAMETERS LIKE 'STATEMENT%' IN ACCOUNT;

Before you pick a number, see how long successful queries really take:

SELECT warehouse_name,
       COUNT(*) AS queries,
       ROUND(APPROX_PERCENTILE(total_elapsed_time, 0.99) / 1000) AS p99_seconds,
       ROUND(MAX(total_elapsed_time) / 1000) AS max_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
  AND warehouse_name IS NOT NULL
GROUP BY warehouse_name
ORDER BY max_seconds DESC;

Then set the limits above that real maximum:

ALTER WAREHOUSE bi_wh SET
  STATEMENT_TIMEOUT_IN_SECONDS = 1800
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

-- A longer limit for a pipeline user on its own warehouse
ALTER USER svc_etl SET STATEMENT_TIMEOUT_IN_SECONDS = 14400;

Warning

A timeout set too low cancels legitimate loads. Keep long ELT work on its own warehouse with its own limit, rather than sharing a short BI limit with it.

#Clean up queries from lost clients

By default, a query keeps running after the client disappears. With ABORT_DETACHED_QUERY = TRUE, in-progress queries are aborted about 5 minutes after client heartbeats stop. Test it on any job that submits a query and then disconnects, because that job would be cancelled too.

ALTER USER analyst_jane SET ABORT_DETACHED_QUERY = TRUE;

#Make the owner easy to find

Have pipelines set a tag, so the Step 1 output tells you who to call:

ALTER SESSION SET QUERY_TAG = 'team=finance;job=daily_orders';

Give each service its own user, so ALTER USER ... ABORT ALL QUERIES stops one workload rather than several.