monitoring-background-jobs

What this skill does

# Monitoring Background Jobs

Monitors background job health by identifying failed, paused, and long-running jobs that are distinct from user queries. Uses SQL-only interfaces (SHOW JOBS and SHOW AUTOMATIC JOBS) to surface schema changes, backups/restores, automatic statistics collection, and SQL stats compaction without requiring DB Console access.

## Prerequisites

- SQL connection with `VIEWJOB` system privilege (read-only) or `CONTROLJOB` role option (control)
- Background jobs are excluded from `SHOW CLUSTER STATEMENTS` and from statement statistics surfaced in the DB Console SQL Activity page

**Related skills:** [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) for live queries, [profiling-statement-fingerprints](../profiling-statement-fingerprints/SKILL.md) for historical query analysis.

## Key Interfaces

- `SHOW JOBS`: User-initiated + automatic jobs (last 12h default; retention configurable via the `jobs.retention_time` cluster setting, default 14 days)
- `SHOW AUTOMATIC JOBS`: Automatic jobs only (AUTO CREATE STATS, SCHEMA CHANGE GC, etc.)

See [job types reference](references/job-types.md) and [job states reference](references/job-states.md) for complete catalogs.

## Core Diagnostic Queries

### Query 1: Failed Jobs (Last 12 Hours)

Identify jobs that failed with error messages:

```sql
-- Failed jobs in last 12 hours
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  finished,
  now() - created AS total_duration,
  error
FROM j
WHERE status = 'failed'
  AND created > now() - INTERVAL '12 hours'
ORDER BY created DESC
LIMIT 50;
```

**Key columns:**
- `error`: Failure reason (check for permission errors, disk space, network issues)
- `description`: Human-readable description of what the job was doing
- `total_duration`: How long the job ran before failing

**Common failure patterns:**
- Permission denied: User lacks required privileges
- Disk space: Backup destination full
- Network timeout: External storage unreachable
- Constraint violation: Restore conflicts with existing data

### Query 2: Long-Running Jobs

Find jobs running longer than expected threshold:

```sql
-- Jobs running longer than 1 hour
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  status,
  running_status,
  created,
  now() - created AS running_for,
  fraction_completed,
  coordinator_id
FROM j
WHERE status = 'running'
  AND created < now() - INTERVAL '1 hour'
ORDER BY created
LIMIT 50;
```

**Key columns:**
- `running_for`: Total elapsed time since job started
- `fraction_completed`: Progress estimate (0.0 to 1.0, NULL if unavailable)
- `running_status`: Sub-state details (e.g., "waiting for MVCC GC")

**Customizable thresholds:**
- Schema changes: 30 minutes to several hours (depends on table size)
- Backups: 1-6+ hours (depends on data volume)
- Automatic jobs: Usually < 30 minutes

### Query 3: Paused Jobs

Identify jobs that are paused and may need attention:

```sql
-- Paused jobs needing resume
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  now() - created AS paused_for,
  coordinator_id
FROM j
WHERE status = 'paused'
ORDER BY created
LIMIT 50;
```

**Action required:**
Resume with `RESUME JOB <job_id>` after verifying the pause reason.

**Common reasons for paused jobs:**
- Manual user pause for maintenance
- Resource constraints (cluster paused the job)
- Error requiring manual intervention

### Query 4: Schema Changes Waiting for MVCC GC

Find SCHEMA CHANGE GC jobs waiting for garbage collection:

```sql
-- Schema change cleanup jobs waiting for GC
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  now() - created AS waiting_for,
  running_status
FROM j
WHERE status = 'running'
  AND job_type = 'SCHEMA CHANGE GC'
  AND running_status LIKE '%waiting for MVCC GC%'
ORDER BY created
LIMIT 50;
```

**Interpretation:**
- **Normal:** SCHEMA CHANGE GC jobs wait for data to become garbage-collectable based on the zone-level `gc.ttlseconds` (default 4 hours)
- **Expected duration:** Up to `gc.ttlseconds` + some overhead
- **When to worry:** Waiting > 2x `gc.ttlseconds` (check the effective value with `SHOW ZONE CONFIGURATION FOR ...` against the affected table, database, or RANGE — zone configs cascade and may be overridden at any level)

**Why this happens:**
After DROP TABLE/INDEX operations, CockroachDB must wait for all reads at older timestamps to complete before physically removing data. This prevents "time-travel" queries from failing.

See [job states reference](references/job-states.md) for detailed MVCC GC explanation.

### Query 5: Automatic Job Health (24h Window)

Monitor automatic background jobs like statistics collection:

```sql
-- Automatic jobs in last 24 hours
SELECT
  job_id,
  job_type,
  description,
  status,
  created,
  finished,
  COALESCE(finished, now()) - created AS duration
FROM [SHOW AUTOMATIC JOBS]
WHERE created > now() - INTERVAL '24 hours'
  AND job_type IN ('AUTO CREATE STATS', 'AUTO SQL STATS COMPACTION')
ORDER BY created DESC
LIMIT 50;
```

**Key job types:**
- `AUTO CREATE STATS`: Automatic table statistics refresh (critical for query optimizer)
- `AUTO SQL STATS COMPACTION`: Periodic cleanup of statement/transaction statistics tables

**Health indicators:**
- **Healthy:** Regular successful executions (every few hours)
- **Unhealthy:** No recent executions, or high failure rate
- **Impact of failure:** Stale statistics lead to poor query plans and slow queries

### Query 6: Jobs by Type and Status

Aggregated view for pattern analysis:

```sql
-- Job distribution by type and status (last 24h)
WITH j AS (SHOW JOBS)
SELECT
  job_type,
  status,
  COUNT(*) AS job_count,
  MIN(created) AS oldest,
  MAX(created) AS newest
FROM j
WHERE created > now() - INTERVAL '24 hours'
GROUP BY job_type, status
ORDER BY job_type, status;
```

**Use case:**
- Identify patterns (e.g., all BACKUP jobs failing, multiple schema changes stuck)
- Spot anomalies (e.g., unusual job type volume)
- Track job success rates by type

### Query 7: Backup and Restore Progress

Track progress of backup/restore jobs:

```sql
-- Active backup/restore jobs with progress
WITH j AS (SHOW JOBS)
SELECT
  job_id,
  job_type,
  description,
  created,
  now() - created AS running_for,
  ROUND(COALESCE(fraction_completed, 0) * 100, 2) AS percent_complete,
  CASE
    WHEN fraction_completed > 0 AND fraction_completed < 1 THEN
      ((now() - created) / fraction_completed) - (now() - created)
    ELSE NULL
  END AS estimated_time_remaining,
  running_status
FROM j
WHERE status = 'running'
  AND job_type IN ('BACKUP', 'RESTORE')
ORDER BY created
LIMIT 50;
```

**Key columns:**
- `percent_complete`: Progress percentage (0-100)
- `estimated_time_remaining`: Rough estimate based on current progress rate
- `running_status`: Detailed status (e.g., "performing backup to s3://...")

**Note:** `fraction_completed` may be NULL for some job types or early in execution.

## Common Workflows

### Workflow 1: Schema Change Stuck Investigation

**Scenario:** User reports ALTER TABLE or CREATE INDEX appears stuck.

1. **Check for running schema changes:**
   ```sql
   WITH j AS (SHOW JOBS)
   SELECT job_id, description, created, now() - created AS running_for,
          fraction_completed, running_status
   FROM j
   WHERE status = 'running'
     AND job_type IN ('SCHEMA CHANGE', 'NEW SCHEMA CHANGE')
   ORDER BY created;
   ```

2. **Identify MVCC GC waits:**
   ```sql
   -- Use Query 4 to find "waiting for MVCC GC" jobs
   ```

3. **Interpret results:**
   - If `running_status` = "waiting for MVCC GC": Normal for post-DROP cleanup (wait up to `gc.ttlseconds`)
   - If long-running with low `fraction_completed`: Check for contention, large table size, or resource constraints
   - If failed: Check `error` column for specific failure reason

4. **Next steps:**
   - MVCC GC wait: Verify the effective `gc.ttlseconds` with `SHOW ZONE CONFIGURATION FOR TABLE/DATABASE/