How to find running queries and kill them

To find the executing queries you can use the following code where you can select the start interval from now, the duration of the query, and the status type. Get rid of the comment at the beginning of and EXECUTION_STATUS='RUNNING' if you just want to find the running queries.

select
    QUERY_ID,
    QUERY_TEXT,
    EXECUTION_STATUS,
    USER_NAME,
    WAREHOUSE_NAME,
    START_TIME,
    END_TIME,
    DATEDIFF(second, START_TIME, END_TIME) as run_time_seconds
from TABLE(information_schema.query_history())
where 
-- Run time durations in seconds
DATEDIFF(second, START_TIME, END_TIME) > 1
-- Start run time in hours
and START_TIME > DATEADD(hour, -1, current_timestamp())
-- Remove comments to find only queries that are still running
--and EXECUTION_STATUS='RUNNING'
order by START_TIME;

 

If you want to narrow down the search area you can use the following to find the running queries for a specific warehouse.

select
    QUERY_ID,
    QUERY_TEXT,
    EXECUTION_STATUS,
    USER_NAME,
    WAREHOUSE_NAME,
    START_TIME,
    END_TIME,
    DATEDIFF(second, START_TIME, END_TIME) as run_time_seconds
from TABLE(information_schema.query_history_by_warehouse(
    WAREHOUSE_NAME => 'MY_WAREHOUSE'))
where 
-- Run time durations in seconds
DATEDIFF(second, START_TIME, END_TIME) > 1
-- Start run time in hours
and START_TIME > DATEADD(hour, -1, current_timestamp())
-- Remove comments to find only queries that are still running
--and EXECUTION_STATUS='RUNNING'
order by START_TIME;

Once you have found the query you want to stop, you can use the following instructions to kill it. Please note that the query ID should be something like 01a00aa0-0000-000a-0000-000a000000aa

SELECT SYSTEM$cancel_query('QUERY_ID');