> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# PAUSE JOB
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `PAUSE JOB` statement lets you pause the following types of jobs:
* `IMPORT` jobs
* `BACKUP` and `RESTORE` jobs
* User-created table statistics jobs
* Automatic table statistics jobs
* Changefeeds
* Schema change jobs
* Scheduled backup jobs
After pausing jobs, you can resume them with `RESUME JOB`.
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` role option set. Non-admin users cannot pause admin users' jobs.
For changefeeds, users with the `CHANGEFEED` privilege on a set of tables can pause changefeed jobs running on those tables.
## Synopsis
### Pause all jobs by type
## Parameters
| Parameter | Description |
| ------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `job\_id` | The ID of the job you want to pause, which can be found with `SHOW JOBS`. |
| `select\_stmt` | A selection query 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 in the statement (`FOR SCHEDULES `). 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, restore, or index backfill job ( schema change ) will continue to hold a protected timestamp 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. In turn, this may cause increased disk usage and degraded performance for some workloads. See Protected timestamps and scheduled backups for more detail.
* A paused changefeed job, if `protect_data_from_gc_on_pause` 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`, 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 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` endpoint.
See the following pages for details on metrics:
* Monitor and Debug Changefeeds
* Backup and Restore Monitoring
* Metrics
## Examples
### Pause a single job
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW JOBS;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 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;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```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, use the schedule's `id`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> PAUSE JOBS FOR SCHEDULE 590204387299262465;
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE JOBS FOR SCHEDULES 1
```
You can also pause multiple schedules by nesting a `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';
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE JOBS FOR SCHEDULES 2
```
## See also
* `RESUME JOB`
* `SHOW JOBS`
* `CANCEL JOB`
* `BACKUP`
* `RESTORE`
* `IMPORT INTO`
* `CREATE CHANGEFEED`