SHOW JOBS

On this page

Warning:

As of May 16, 2023, CockroachDB v21.2 is no longer supported. For more details, refer to the Release Support Policy.

The SHOW JOBS statement lists all of the types of long-running tasks your cluster has performed in the last 12 hours, including:

Schema changes through ALTER TABLE, DROP DATABASE, DROP TABLE, and TRUNCATE
IMPORT.
Enterprise BACKUP and RESTORE.
Scheduled backups.
User-created table statistics created for use by the cost-based optimizer. To view automatic table statistics, use SHOW AUTOMATIC JOBS.
New in v21.2: SHOW JOBS now displays newly added columns from crdb_internal.jobs (last_run, next_run, num_runs, and execution_errors). The columns capture state related to retries, failures, and exponential backoff.

These details can help you understand the status of crucial tasks that can impact the performance of your cluster, as well as help you control them.

New in v21.2: Details for enterprise changefeeds, including the sink URI and full table name, are not displayed on running the SHOW JOBS statement. For details about enterprise changefeeds, including the sink URI and the full table name, use SHOW CHANGEFEED JOBS.

To block a call to SHOW JOBS that returns after all specified job ID(s) have a terminal state, use SHOW JOBS WHEN COMPLETE. The statement will return a row per job ID, which provides details of the job execution. Note that while this statement is blocking, it will time out after 24 hours.

Considerations

The SHOW JOBS statement shows only long-running tasks.
For jobs older than 12 hours, query the crdb_internal.jobs table.
Jobs are deleted after 14 days. This interval can be changed via the jobs.retention_time cluster setting.
While the SHOW JOBS WHEN COMPLETE statement is blocking, it will time out after 24 hours.
Garbage collection jobs are created for dropped tables and dropped indexes, and will execute after the GC TTL has elapsed (default is 25 hours). These jobs cannot be canceled.
New in v21.2: CockroachDB automatically retries jobs that fail due to retry errors or job coordination failures, with exponential backoff. The jobs.registry.retry.initial_delay cluster setting sets the initial delay between retries and jobs.registry.retry.max_delay sets the maximum delay.

Required privileges

By default, only the root user can execute SHOW JOBS.

Synopsis

Parameters

Parameter	Description
`SHOW AUTOMATIC JOBS`	Show automatic table statistics. For an example, see Show automatic jobs.
`SHOW JOBS WHEN COMPLETE`	Block `SHOW JOB` until the provided job ID reaches a terminal state. For an example, see Show job when complete.
`select_stmt`	A selection query that specifies the `job_id`(s) to view.
`job_id`	The ID of the job to view.
`for_schedules_clause`	The schedule you want to view jobs for. You can view jobs for a specific schedule (`FOR SCHEDULE id`) or view jobs for multiple schedules by nesting a `SELECT` clause in the statement (`FOR SCHEDULES <select_clause>`). For an example, see Show jobs for a schedule.
`SHOW CHANGEFEED JOBS`	New in v21.2: Show details about enterprise changefeeds, including the sink URI and the full table name. For an example, see Show changefeed jobs.

Response

The output of SHOW JOBS lists ongoing jobs first, then completed jobs within the last 12 hours. The list of ongoing jobs is sorted by starting time, whereas the list of completed jobs is sorted by finished time.

The following fields are returned for each job:

Field	Description
`job_id`	A unique ID to identify each job. This value is used if you want to control jobs (i.e., pause, resume, or cancel it).
`job_type`	The type of job. Possible values: `SCHEMA CHANGE`, `BACKUP`, `RESTORE`, `IMPORT`, and `CREATE STATS`. For `SHOW AUTOMATIC JOBS`, the possible value is `AUTO CREATE STATS`.
`description`	The statement that started the job, or a textual description of the job.
`statement`	When `description` is a textual description of the job, the statement that started the job is returned in this column. Currently, this field is populated only for the automatic table statistics jobs.
`user_name`	The name of the user who started the job.
`status`	The job's current state. Possible values: `pending`, `running`, `paused`, `failed`, `succeeded`, or `canceled`.
`running_status`	The job's detailed running status, which provides visibility into the progress of the dropping or truncating of tables (i.e., `DROP TABLE`, `DROP DATABASE`, or `TRUNCATE`). For dropping or truncating jobs, the detailed running status is determined by the status of the table at the earliest stage of the schema change. The job is completed when the GC TTL expires and both the table data and ID is deleted for each of the tables involved. Possible values: `draining names`, `waiting for GC TTL`, `RocksDB compaction`, or `NULL` (when the status cannot be determined). For the `SHOW AUTOMATIC JOBS` statement, the value of this field is `NULL`.
`created`	The `TIMESTAMP` when the job was created.
`started`	The `TIMESTAMP` when the job began running first.
`finished`	The `TIMESTAMP` when the job was `succeeded`, `failed`, or `canceled`.
`modified`	The `TIMESTAMP` when the job had anything modified.
`fraction_completed`	The fraction (between `0.00` and `1.00`) of the job that's been completed.
`error`	If the job `failed`, the error generated by the failure.
`coordinator_id`	The ID of the node running the job.
`trace_id`	New in v21.2: The job's trace ID, for inflight debugging.
`last_run`	New in v21.2: The `TIMESTAMP` of the last attempted execution.
`next_run`	New in v21.2: The `TIMESTAMP` of the next attempted execution.
`num_runs`	New in v21.2: The number of job execution attempts.
`execution_errors`	New in v21.2: A list of any execution errors that the job encountered.

For details of changefeed-specific responses, see SHOW CHANGEFEED JOBS.

Examples

Show jobs

> SHOW JOBS;

    job_id      | job_type  |               description                 |...
+---------------+-----------+-------------------------------------------+...
 27536791415282 |  RESTORE  | RESTORE db.* FROM 'azure://backup/db/tbl' |...

Filter jobs

You can filter jobs by using SHOW JOBS as the data source for a SELECT statement, and then filtering the values with the WHERE clause.

> SELECT * FROM [SHOW JOBS] WHERE job_type = 'RESTORE' AND status IN ('running', 'failed') ORDER BY created DESC;

    job_id      | job_type  |              description                  |...
+---------------+-----------+-------------------------------------------+...
 27536791415282 |  RESTORE  | RESTORE db.* FROM 'azure://backup/db/tbl' |...

Show automatic jobs

> SHOW AUTOMATIC JOBS;

    job_id           |       job_type      |                    description                      |...
+--------------------+---------------------+-----------------------------------------------------+...
  438235476849557505 | AUTO CREATE STATS   | Table statistics refresh for defaultdb.public.users |...
(1 row)

Filter automatic jobs

You can filter jobs by using SHOW AUTOMATIC JOBS as the data source for a SELECT statement, and then filtering the values with the WHERE clause.

> SELECT * FROM [SHOW AUTOMATIC JOBS] WHERE status = ('succeeded') ORDER BY created DESC;

    job_id           |       job_type      |                    description                      | ...
+--------------------+---------------------+-----------------------------------------------------+ ...
  438235476849557505 | AUTO CREATE STATS   | Table statistics refresh for defaultdb.public.users | ...
(1 row)

Show changefeed jobs

New in v21.2: You can display specific fields relating to changefeed jobs by running SHOW CHANGEFEED JOBS. These fields include:

high_water_timestamp: Guarantees all changes before or at this time have been emitted.
sink_uri: The destination URI of the configured sink for a changefeed.
full_table_names: The full name resolution for a table. For example, defaultdb.public.mytable refers to the defaultdb database, the public schema, and the table mytable table.

> SHOW CHANGEFEED JOBS;

    job_id             |                                                                                   description                                                                  | ...
  685724608744325121   | CREATE CHANGEFEED FOR TABLE mytable INTO 'kafka://localhost:9092' WITH confluent_schema_registry = 'http://localhost:8081', format = 'avro', resolved, updated | ...
  685723987509116929   | CREATE CHANGEFEED FOR TABLE mytable INTO 'kafka://localhost:9092' WITH confluent_schema_registry = 'http://localhost:8081', format = 'avro', resolved, updated | ...
(2 rows)

Changefeed jobs can be paused, resumed, or canceled.

Filter changefeed jobs

You can filter jobs by using SHOW CHANGEFEED JOBS as the data source for a SELECT statement, and then filtering the values with a WHERE clause. For example, you can filter by the status of changefeed jobs:

SELECT * FROM [SHOW CHANGEFEED JOBS] WHERE status = ('paused');

    job_id           |                                                              description         | ...
  685723987509116929 | CREATE CHANGEFEED FOR TABLE mytable INTO 'kafka://localhost:9092' WITH confluent | ...
(1 row)

Show schema changes

You can show just schema change jobs by using SHOW JOBS as the data source for a SELECT statement, and then filtering the job_type value with the WHERE clause:

> SELECT * FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE';

    job_id       | job_type        |              description                           |...
+----------------+-----------------+----------------------------------------------------+...
  27536791415282 |  SCHEMA CHANGE  | ALTER TABLE test.public.foo ADD COLUMN bar VARCHAR |...

Scheme change jobs can be paused, resumed, and canceled.

Show job when complete

To block SHOW JOB until the provided job ID reaches a terminal state, use SHOW JOB WHEN COMPLETE:

> SHOW JOB WHEN COMPLETE 27536791415282;

    job_id       | job_type  |               description                 |...
+----------------+-----------+-------------------------------------------+...
  27536791415282 |  RESTORE  | RESTORE db.* FROM 'azure://backup/db/tbl' |...

Show jobs for a schedule

To view jobs for a specific backup schedule, use the schedule's id:

> SHOW JOBS FOR SCHEDULE 590204387299262465;

    job_id           | job_type |              description                                          |...
+--------------------+----------+-------------------------------------------------------------------+...
  590205481558802434 | BACKUP   | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup| ...
(1 row)

You can also view multiple schedules by nesting a SELECT clause that retrieves id(s) inside the SHOW JOBS statement:

> SHOW JOBS FOR SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'test_schedule';

    job_id           | job_type  |              description                  |...
+--------------------+-----------+-------------------------------------------+...
  590204496007299074 | BACKUP    | BACKUP INTO '/2020/09/15-161444.99' IN'   |...
(2 rows)

Cockroach
University

Docs Hub

SHOW JOBS

Considerations

Required privileges

Synopsis

Parameters

Response

Examples

Show jobs

Filter jobs

Show automatic jobs

Filter automatic jobs

Show changefeed jobs

Filter changefeed jobs

Show schema changes

Show job when complete

Show jobs for a schedule

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

SHOW JOBS

Considerations

Required privileges

Synopsis

Parameters

Response

Examples

Show jobs

Filter jobs

Show automatic jobs

Filter automatic jobs

Show changefeed jobs

Filter changefeed jobs

Show schema changes

Show job when complete

Show jobs for a schedule

See also

Cockroach
University

Cockroach
University