The CANCEL JOB
statement lets you stop long-running jobs, which include:
IMPORT
jobsBACKUP
andRESTORE
jobs- User-created table statistics jobs
- Automatic table statistics jobs
- Changefeeds
- Scheduled backup jobs
- Schema change jobs (see Limitations for exceptions)
Limitations
- When an Enterprise
RESTORE
is canceled, partially restored data is properly cleaned up. This can have a minor, temporary impact on cluster performance. - To avoid transaction states that cannot properly roll back,
DROP
statements (e.g.,DROP TABLE
),ALTER ... RENAME
statements (e.g.,ALTER TABLE ... RENAME TO
), andCREATE TABLE ... AS
statements are no longer cancellable.
Required privileges
To cancel a job, the user must be a member of the admin
role or must have the CONTROLJOB
role option set. Non-admin users cannot cancel admin users' jobs.
For changefeeds, users with the CHANGEFEED
privilege on a set of tables can cancel changefeed jobs running on those tables.
Synopsis
Cancel all jobs by type
Parameters
Parameter | Description |
---|---|
job_id |
The ID of the job you want to cancel, which can be found with SHOW JOBS . |
select_stmt |
A selection query that returns job_id (s) to cancel. |
for_schedules_clause |
The schedule you want to cancel jobs for. You can cancel jobs for a specific schedule (FOR SCHEDULE id ) or cancel jobs for multiple schedules by nesting a SELECT clause in the statement (FOR SCHEDULES <select_clause> ). See the examples below. |
BACKUP , CHANGEFEED , RESTORE , IMPORT |
The job type to cancel. |
Examples
Cancel a single job
> SHOW JOBS;
+----------------+---------+------------------------------------------------+...
| id | type | description |...
+----------------+---------+------------------------------------------------+...
| 27536791415282 | RESTORE | RESTORE db.* FROM 'azure-blob://backup/db/tbl' |...
+----------------+---------+------------------------------------------------+...
> CANCEL JOB 27536791415282;
Cancel multiple jobs
To cancel multiple jobs, nest a SELECT
clause that retrieves job_id
(s) inside the CANCEL JOBS
statement:
> CANCEL JOBS (WITH x AS (SHOW JOBS) SELECT job_id FROM x
WHERE user_name = 'maxroach');
All jobs created by maxroach
will be cancelled.
Cancel by job type
To cancel all jobs by the type of job, use the CANCEL ALL {job} JOBS
statement. You can cancel all BACKUP
, RESTORE
, CHANGEFEED
, IMPORT
jobs using this statement, for example:
CANCEL ALL BACKUP JOBS;
Cancel automatic table statistics jobs
Canceling an automatic table statistics job is not useful since the system will automatically restart the job immediately. To permanently disable automatic table statistics jobs, disable the sql.stats.automatic_collection.enabled
cluster setting:
> SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
Cancel jobs for a schedule
To cancel jobs for a specific backup schedule, use the schedule's id
:
> CANCEL JOBS FOR SCHEDULE 590204387299262465;
CANCEL JOBS FOR SCHEDULES 1
You can also CANCEL multiple schedules by nesting a SELECT
clause that retrieves id
(s) inside the CANCEL JOBS
statement:
> CANCEL JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
CANCEL JOBS FOR SCHEDULES 2