SHOW JOBS

On this page

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.
User-created table statistics created for use by the cost-based optimizer.
The automatic table statistics are not displayed on running the SHOW JOBS statement. To view the automatic table statistics, use SHOW AUTOMATIC JOBS.

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.

Considerations

The SHOW JOBS statement shows only long-running tasks. For an exhaustive list of jobs running in the cluster, use the SQL Audit Logging (Experimental) feature.
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.

Required privileges

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

Synopsis

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`	New in v19.1: 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.
`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.

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 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 |...

Pricing

Contact us

Sign In