> ## 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](/docs/stable/sql-statements.md) lets you pause the following types of jobs:

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

After pausing jobs, you can resume them with [`RESUME JOB`](/docs/stable/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`](/docs/stable/create-user#create-a-user-that-can-pause-resume-and-cancel-non-admin-jobs) [role option](/docs/stable/security-reference/authorization.md) set. Non-admin users cannot pause admin users' jobs.

For changefeeds, users with the [`CHANGEFEED`](/docs/stable/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/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/pause_job.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=f758d0941dd2cdb8d3db7dfa3814cd4a)

### Pause all jobs by type

![pause\_all\_jobs syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/pause_all_jobs.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=32165c3f837b77f472bc774e68cebc32)

## Parameters

| Parameter | Description |
| - | - |
| `job_id` | The ID of the job you want to pause, which can be found with [`SHOW JOBS`](/docs/stable/show-jobs). |
| `select_stmt` | A [selection query](/docs/stable/selection-queries.md) 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](/docs/stable/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](/docs/stable/backup.md), [restore](/docs/stable/restore.md), or index backfill job ([schema change](/docs/stable/online-schema-changes.md)) will continue to hold a [protected timestamp](/docs/stable/architecture/storage-layer.md#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](/docs/stable/architecture/storage-layer.md#garbage-collection). In turn, this may cause increased disk usage and degraded performance for some workloads. See [Protected timestamps and scheduled backups](/docs/stable/create-schedule-for-backup.md#protected-timestamps-and-scheduled-backups) for more detail.
* A paused [changefeed](/docs/stable/create-changefeed.md) job, if [`protect_data_from_gc_on_pause`](/docs/stable/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`](/docs/stable/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](/docs/stable/protect-changefeed-data.md) 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 [Prometheus endpoint](/docs/stable/prometheus-endpoint.md).

See the following pages for details on metrics:

* [Monitor and Debug Changefeeds](/docs/stable/monitor-and-debug-changefeeds.md)
* [Backup and Restore Monitoring](/docs/stable/backup-and-restore-monitoring.md)
* [Metrics](/docs/stable/metrics.md)

## 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](/docs/stable/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](/docs/stable/cluster-settings.md):

```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](/docs/stable/create-schedule-for-backup.md), 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](/docs/stable/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`](/docs/stable/resume-job)
* [`SHOW JOBS`](/docs/stable/show-jobs)
* [`CANCEL JOB`](/docs/stable/cancel-job)
* [`BACKUP`](/docs/stable/backup)
* [`RESTORE`](/docs/stable/restore)
* [`IMPORT INTO`](/docs/stable/import-into)
* [`CREATE CHANGEFEED`](/docs/stable/create-changefeed)