Snowflake TASK got stuck in STARTED state

Question

I Created TASK like below

CREATE OR REPLACE TASK test_task
WAREHOUSE = TEST_WH
SCHEDULE = '1 MINUTE'
AS
BEGIN
 CALL SP_TASKEXECUTION('CREATE_TABLE_PROC_NAME');
END;

I see task created and in suspended state. Now i ran ALTER TASK test_task to RESUME the task. Now im seeing the task status as started. But it is not running. Also i tried to EXECUTE the task Still it is in started state

SP_TASKEXECUTION : This procedure takes another procedure name as input and execute.

CREATE_TABLE_PROC_NAME : This procedure contains create or replace table statment.

Both are working fine when i executes manually. but when it is scheduled through tasks it got stuck in started state .

My question is what can be the possible reasons and how to fix this?

Answer 1

If you are only checking TASK status using SHOW TASKS, then it only has two state as per documentation.

‘started’ or ‘suspended’ based on the current state of the task.

I think you would want to check TASK_HISTORY view which should show the previously executed tasks.

Sample query from documentation

    SELECT query_text, completed_time
FROM snowflake.account_usage.task_history
ORDER BY completed_time DESC
LIMIT 10;

Note as per documentation :

Latency for the view may be up to 45 minutes.

Answer 2

SHOW tasks will show only 2 states ‘started’ or ‘suspended’. To view the past executions of task Realtime(without latency) task data of previous 7 days: https://docs.snowflake.com/en/sql-reference/functions/task_history

Task data for last 1 year with 45 minute latency: https://docs.snowflake.com/en/sql-reference/account-usage/task_history