Runaway queries on a Databricks SQL warehouse: find them, cancel them and enforce STATEMENT_TIMEOUT

#Symptom

• Dashboards on a shared SQL warehouse are slow and queries sit in the queue.
• The warehouse has scaled to its maximum cluster count and never scales down or auto-stops.
• Someone reports "my query has been running for six hours".
• DBU spend for one warehouse is far above its normal daily level.

One or a few long statements are usually the cause: a cross join, a missing filter on a large fact table, or a BI tool retrying a query that never finishes. By default a statement can run for up to 172800 seconds (2 days).

#Step 1: list what is running now

#In the UI

Open Query History, filter by the warehouse and status Running, and sort by duration. Click a query to see its text, user, source (dashboard, notebook, BI tool) and query profile.

#With the Databricks SDK for Python

Useful when you need the list in a ticket or want to script it. Run from a notebook or a laptop with workspace auth configured:

import time
from databricks.sdk import WorkspaceClient
from databricks.sdk.service.sql import QueryFilter, QueryStatus

w = WorkspaceClient()
WAREHOUSE_ID = "<warehouse-id>"

resp = w.query_history.list(
    filter_by=QueryFilter(statuses=[QueryStatus.RUNNING], warehouse_ids=[WAREHOUSE_ID]),
    max_results=100,
)
queries = getattr(resp, "res", None) or list(resp)   # response shape varies by SDK version

now_ms = int(time.time() * 1000)
for q in sorted(queries, key=lambda q: q.query_start_time_ms or now_ms):
    mins = (now_ms - (q.query_start_time_ms or now_ms)) / 60000
    print(f"{mins:7.1f} min  {q.query_id}  {q.user_name}  {(q.query_text or '')[:80]!r}")

Filter on one status per call. The API documentation recommends separate calls rather than several statuses at once. Repeat with QueryStatus.QUEUED to see what is waiting behind the long runners.

Find the warehouse ID with:

databricks warehouses list --output json | jq -r '.[] | [.id, .name, .state, .num_clusters] | @tsv'

#Step 2: decide what to cancel

Warning

Cancelling an ETL statement such as MERGE, INSERT OVERWRITE or CREATE OR REPLACE TABLE stops the write. Delta does not commit a partial result, but the downstream job fails and must be rerun. Check with the owner before cancelling a service principal's write.

#Step 3: cancel

#From Query History

Open the query and click Cancel next to the status. The button only appears while the query is running. Statements running on Lakeflow pipeline compute can only be cancelled from the Pipelines UI.

#Statements submitted through the Statement Execution API

If your automation submitted the statement through /api/2.0/sql/statements, cancel it with the statement ID:

databricks api post /api/2.0/sql/statements/<statement-id>/cancel
databricks api get /api/2.0/sql/statements/<statement-id> | jq '.status.state'

The cancel call returns an empty response once the request is received. Poll the statement until its state is CANCELED.

#Last resort: stop the warehouse

databricks warehouses stop <warehouse-id>
databricks warehouses start <warehouse-id>

Caution

Stopping a warehouse kills every running and queued statement on it, including other teams' dashboards and ETL writes. Announce it in the incident channel first and use it only when individual cancels have failed.

#Step 4: enforce a timeout

STATEMENT_TIMEOUT is a per-statement limit in seconds. It can be set at three scopes. The first non-null value wins, in this order: session, warehouse (Beta), workspace. If none is set, the 2-day system default applies.

#Session

For a notebook, SQL editor tab or connection that should not run long:

SET STATEMENT_TIMEOUT = 3600;   -- one hour
SET STATEMENT_TIMEOUT;          -- show the value in effect

Many drivers can also pass it as a connection property. The Databricks JDBC driver has a statement_timeout property. Check your driver's documentation for the exact name.

#Workspace

A workspace admin sets the default for all SQL warehouses: Settings > Compute > SQL warehouses > Manage > SQL Configuration Parameters, then add:

STATEMENT_TIMEOUT 7200

Warning

Saving a change to SQL configuration parameters restarts all running SQL warehouses in the workspace. Running queries fail. Make the change in a maintenance window.

#Warehouse (Beta)

A per-warehouse timeout exists as a Beta, API-only setting. A workspace admin must turn on the Warehouse Statement Timeout preview on the Previews page first. Use it to give an ad-hoc warehouse a shorter limit than an ETL warehouse. Check the current Warehouses API reference for the field name before scripting it.

#Choosing values

• Base the limit on real durations from Step 5. Set it above the 99th-percentile duration of legitimate queries, not at a round number you guessed.
• Use shorter limits on interactive and BI warehouses (often one to two hours) and longer ones, or the default, on ETL warehouses.
• Long-running ETL that should not be capped is a sign it belongs on job compute, not on a shared warehouse.

#Classic and job clusters

STATEMENT_TIMEOUT applies to SQL warehouses. For SQL run on clusters, use job task timeouts (timeout_seconds on the task) for scheduled work. Query Watchdog can kill interactive queries whose output rows far exceed their input rows, which catches most accidental cross joins:

spark.databricks.queryWatchdog.enabled true
spark.databricks.queryWatchdog.outputRatioThreshold 1000

Set these in the cluster's Spark config or in a compute policy.

#Step 5: find repeat offenders

system.query.history keeps completed statements, including those that timed out or were cancelled. Long queries per user and warehouse over the last week:

SELECT
  executed_by,
  compute.warehouse_id,
  execution_status,
  COUNT(*)                                   AS queries,
  ROUND(MAX(total_duration_ms) / 60000, 1)   AS max_minutes,
  ROUND(SUM(total_duration_ms) / 3600000, 1) AS total_hours
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 7 DAYS
  AND compute.warehouse_id IS NOT NULL
  AND total_duration_ms > 30 * 60 * 1000          -- longer than 30 minutes
GROUP BY ALL
ORDER BY total_hours DESC
LIMIT 50;

Duration percentiles per warehouse, to set the timeout:

SELECT
  compute.warehouse_id,
  ROUND(percentile(total_duration_ms, 0.95) / 1000) AS p95_seconds,
  ROUND(percentile(total_duration_ms, 0.99) / 1000) AS p99_seconds,
  ROUND(MAX(total_duration_ms) / 1000)              AS max_seconds
FROM system.query.history
WHERE start_time >= current_timestamp() - INTERVAL 30 DAYS
  AND execution_status = 'FINISHED'
  AND compute.warehouse_id IS NOT NULL
GROUP BY ALL;

You need SELECT on system.query granted by a metastore or account admin. Column names can change as system tables evolve. Run DESCRIBE system.query.history if a column is missing.

#Prevent it next time

• Set a workspace STATEMENT_TIMEOUT based on measured p99 durations, and shorter session or warehouse limits for ad-hoc use.
• Separate warehouses for BI dashboards, ad-hoc analysis and ETL, so one bad query cannot queue everyone.
• Cap maximum clusters on each warehouse so a runaway workload cannot scale cost without limit.
• Schedule the Step 5 query as a weekly report or a Databricks SQL alert, and follow up with the top users.
• Review dashboard refresh schedules and BI tool retry settings when the same query text keeps showing up.