> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PAUSE JOB

The `PAUSE JOB` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) lets you pause the following types of jobs:

* [`IMPORT`](https://docs.cockroachlabs.com/docs/v24.2/import-into) jobs
* [`BACKUP`](https://docs.cockroachlabs.com/docs/v24.2/backup) and [`RESTORE`](https://docs.cockroachlabs.com/docs/v24.2/restore) jobs
* [User-created table statistics](https://docs.cockroachlabs.com/docs/v24.2/create-statistics) jobs
* [Automatic table statistics](https://docs.cockroachlabs.com/docs/v24.2/cost-based-optimizer#table-statistics) jobs
* [Changefeeds](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed)
* [Schema change](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes) jobs
* [Scheduled backup](https://docs.cockroachlabs.com/docs/v24.2/manage-a-backup-schedule) jobs

After pausing jobs, you can resume them with [`RESUME JOB`](https://docs.cockroachlabs.com/docs/v24.2/resume-job).

> [!NOTE]
>
> If a schema change job is paused, any jobs waiting on that schema change will stop waiting and return an error.

## Required privileges

To pause a job, the user must be a member of the `admin` role or must have the [`CONTROLJOB`](https://docs.cockroachlabs.com/docs/v24.2/create-user#create-a-user-that-can-pause-resume-and-cancel-non-admin-jobs) [role option](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization) set. Non-admin users cannot pause admin users' jobs.

For changefeeds, users with the [`CHANGEFEED`](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed#required-privileges) privilege on a set of tables can pause changefeed jobs running on those tables.

## Synopsis

![pause\_job syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/pause_job.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=0101d73e841108ebe5ad1eb8085e1576)

### Pause all jobs by type

![pause\_all\_jobs syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/pause_all_jobs.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=8544d0e864a89c4aef646f529ba4f762)

## Parameters

| Parameter | Description |
| - | - |
| `job_id` | The ID of the job you want to pause, which can be found with [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs). |
| `select_stmt` | A [selection query](https://docs.cockroachlabs.com/docs/v24.2/selection-queries) that returns `job_id`(s) to pause. |
| `for_schedules_clause` | The schedule you want to pause jobs for. You can pause jobs for a specific schedule (`FOR SCHEDULE id`) or pause jobs for multiple schedules by nesting a [`SELECT` clause](https://docs.cockroachlabs.com/docs/v24.2/select-clause) in the statement (`FOR SCHEDULES <select_clause>`). See the [examples](#pause-jobs-for-a-schedule) below. |
| `WITH REASON = ...` | The reason to pause the job. CockroachDB stores the reason in the job's metadata, but there is no way to display it. |
| `BACKUP`, `CHANGEFEED`, `RESTORE`, `IMPORT` | The job type to pause. |

## Monitoring paused jobs

We recommend monitoring paused jobs. Jobs that are paused for a long period of time can start to affect the cluster in the following ways:

* A paused [backup](https://docs.cockroachlabs.com/docs/v24.2/backup), [restore](https://docs.cockroachlabs.com/docs/v24.2/restore), or index backfill job ([schema change](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)) will continue to hold a [protected timestamp](https://docs.cockroachlabs.com/docs/v24.2/architecture/storage-layer#protected-timestamps) record on the data the job is operating on. This could result in data accumulation as the older versions of the keys cannot be [garbage collected](https://docs.cockroachlabs.com/docs/v24.2/architecture/storage-layer#garbage-collection). In turn, this may cause increased disk usage and degraded performance for some workloads. See [Protected timestamps and scheduled backups](https://docs.cockroachlabs.com/docs/v24.2/create-schedule-for-backup#protected-timestamps-and-scheduled-backups) for more detail.
* A paused [changefeed](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed) job, if [`protect_data_from_gc_on_pause`](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed) is set, will also hold a protected timestamp record on the data the job is operating on. Depending on the value of [`gc_protect_expires_after`](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed), this can lead to data accumulation. Once `gc_protect_expires_after` elapses, the protected timestamp record will be released and the changefeed job will be canceled. See [Garbage collection and changefeeds](https://docs.cockroachlabs.com/docs/v24.2/protect-changefeed-data) for more detail.

To avoid these issues, use the `jobs.{job_type}.currently_paused` metric to track the number of jobs (for each job type) that are currently considered paused.

You can monitor protected timestamps relating to particular CockroachDB jobs with the following metrics:

* `jobs.{job_type}.protected_age_sec` tracks the oldest protected timestamp record protecting `{job_type}` jobs. As this metric increases, garbage accumulation increases. Garbage collection will not progress on a table, database, or cluster if the protected timestamp record is present.
* `jobs.{job_type}.protected_record_count` tracks the	number of protected timestamp records held by `{job_type}` jobs.

For a full list of the available job types, access your cluster's [`/_status/vars`](https://docs.cockroachlabs.com/docs/v24.2/monitoring-and-alerting#prometheus-endpoint) endpoint.

See the following pages for details on metrics:

* [Monitor and Debug Changefeeds](https://docs.cockroachlabs.com/docs/v24.2/monitor-and-debug-changefeeds)
* [Backup and Restore Monitoring](https://docs.cockroachlabs.com/docs/v24.2/backup-and-restore-monitoring)
* [Metrics](https://docs.cockroachlabs.com/docs/v24.2/metrics)

## Examples

### Pause a single job

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW JOBS;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOB 27536791415282;
```

### Pause multiple jobs

To pause multiple jobs, nest a [`SELECT` clause](https://docs.cockroachlabs.com/docs/v24.2/select-clause) that retrieves `job_id`(s) inside the `PAUSE JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS (WITH x AS (SHOW JOBS) SELECT job_id FROM x
      WHERE user_name = 'maxroach');
```

All jobs created by `maxroach` will be paused.

### Pause by job type

To pause all jobs by the type of job, use the `PAUSE ALL {job} JOBS` statement. You can pause all `BACKUP`, `RESTORE`, `CHANGEFEED`, `IMPORT` jobs using this statement, for example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE ALL BACKUP JOBS;
```

### Pause automatic table statistics jobs

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW AUTOMATIC JOBS;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOB 438235476849557505;
```

To permanently disable automatic table statistics jobs, disable the `sql.stats.automatic_collection.enabled` [cluster setting](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
```

### Pause jobs for a schedule

To pause jobs for a specific [backup schedule](https://docs.cockroachlabs.com/docs/v24.2/create-schedule-for-backup), use the schedule's `id`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS FOR SCHEDULE 590204387299262465;
```

```
PAUSE JOBS FOR SCHEDULES 1
```

You can also pause multiple schedules by nesting a [`SELECT` clause](https://docs.cockroachlabs.com/docs/v24.2/select-clause) that retrieves `id`(s) inside the `PAUSE JOBS` statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
```

```
PAUSE JOBS FOR SCHEDULES 2
```

## See also

* [`RESUME JOB`](https://docs.cockroachlabs.com/docs/v24.2/resume-job)
* [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs)
* [`CANCEL JOB`](https://docs.cockroachlabs.com/docs/v24.2/cancel-job)
* [`BACKUP`](https://docs.cockroachlabs.com/docs/v24.2/backup)
* [`RESTORE`](https://docs.cockroachlabs.com/docs/v24.2/restore)
* [`IMPORT INTO`](https://docs.cockroachlabs.com/docs/v24.2/import-into)
* [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed)