Update to CockroachDB

{
  "description": "Monitors CockroachDB background job health by identifying failed, paused, and long-running jobs using SHOW JOBS and SHOW AUTOMATIC JOBS. Surfaces schema changes, backups/restores, automatic statistics collection, and SQL stats compaction jobs without DB Console access. Use when investigating schema change delays, failed backups, or automatic job issues.",
  "included_files": [
    {
      "relative_path": "references/job-states.md",
      "size_in_bytes": 15070
    },
    {
      "relative_path": "references/job-types.md",
      "size_in_bytes": 15930
    },
    {
      "relative_path": "references/permissions.md",
      "size_in_bytes": 13315
    },
    {
      "relative_path": "references/sql-query-variations.md",
      "size_in_bytes": 15152
    }
  ],
  "name": "monitoring-background-jobs",
  "skill_md_contents": "---\nname: monitoring-background-jobs\ndescription: Monitors CockroachDB background job health by identifying failed, paused, and long-running jobs using SHOW JOBS and SHOW AUTOMATIC JOBS. Surfaces schema changes, backups/restores, automatic statistics collection, and SQL stats compaction jobs without DB Console access. Use when investigating schema change delays, failed backups, or automatic job issues.\ncompatibility: Requires SQL access with VIEWJOB privilege or CONTROLJOB role option for cluster-wide visibility. Uses SHOW JOBS and SHOW AUTOMATIC JOBS (production-safe).\n---\n\n# Monitoring Background Jobs\n\nMonitors 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.\n\n## Prerequisites\n\n- SQL connection with `VIEWJOB` system privilege (read-only) or `CONTROLJOB` role option (control)\n- Background jobs are excluded from `SHOW CLUSTER STATEMENTS` and from statement statistics surfaced in the DB Console SQL Activity page\n\n**Related skills:** [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) for live queries, [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) for historical query analysis.\n\n## Key Interfaces\n\n- `SHOW JOBS`: User-initiated + automatic jobs (last 12h default; retention configurable via the `jobs.retention_time` cluster setting, default 14 days)\n- `SHOW AUTOMATIC JOBS`: Automatic jobs only (AUTO CREATE STATS, SCHEMA CHANGE GC, etc.)\n\nSee [job types reference](references/job-types.md) and [job states reference](references/job-states.md) for complete catalogs.\n\n## Core Diagnostic Queries\n\n### Query 1: Failed Jobs (Last 12 Hours)\n\nIdentify jobs that failed with error messages:\n\n```sql\n-- Failed jobs in last 12 hours\nWITH j AS (SHOW JOBS)\nSELECT\n  job_id,\n  job_type,\n  description,\n  created,\n  finished,\n  now() - created AS total_duration,\n  error\nFROM j\nWHERE status = 'failed'\n  AND created > now() - INTERVAL '12 hours'\nORDER BY created DESC\nLIMIT 50;\n```\n\n**Key columns:**\n- `error`: Failure reason (check for permission errors, disk space, network issues)\n- `description`: Human-readable description of what the job was doing\n- `total_duration`: How long the job ran before failing\n\n**Common failure patterns:**\n- Permission denied: User lacks required privileges\n- Disk space: Backup destination full\n- Network timeout: External storage unreachable\n- Constraint violation: Restore conflicts with existing data\n\n### Query 2: Long-Running Jobs\n\nFind jobs running longer than expected threshold:\n\n```sql\n-- Jobs running longer than 1 hour\nWITH j AS (SHOW JOBS)\nSELECT\n  job_id,\n  job_type,\n  description,\n  status,\n  running_status,\n  created,\n  now() - created AS running_for,\n  fraction_completed,\n  coordinator_id\nFROM j\nWHERE status = 'running'\n  AND created < now() - INTERVAL '1 hour'\nORDER BY created\nLIMIT 50;\n```\n\n**Key columns:**\n- `running_for`: Total elapsed time since job started\n- `fraction_completed`: Progress estimate (0.0 to 1.0, NULL if unavailable)\n- `running_status`: Sub-state details (e.g., \"waiting for MVCC GC\")\n\n**Customizable thresholds:**\n- Schema changes: 30 minutes to several hours (depends on table size)\n- Backups: 1-6+ hours (depends on data volume)\n- Automatic jobs: Usually < 30 minutes\n\n### Query 3: Paused Jobs\n\nIdentify jobs that are paused and may need attention:\n\n```sql\n-- Paused jobs needing resume\nWITH j AS (SHOW JOBS)\nSELECT\n  job_id,\n  job_type,\n  description,\n  created,\n  now() - created AS paused_for,\n  coordinator_id\nFROM j\nWHERE status = 'paused'\nORDER BY created\nLIMIT 50;\n```\n\n**Action required:**\nResume with `RESUME JOB <job_id>` after verifying the pause reason.\n\n**Common reasons for paused jobs:**\n- Manual user pause for maintenance\n- Resource constraints (cluster paused the job)\n- Error requiring manual intervention\n\n### Query 4: Schema Changes Waiting for MVCC GC\n\nFind SCHEMA CHANGE GC jobs waiting for garbage collection:\n\n```sql\n-- Schema change cleanup jobs waiting for GC\nWITH j AS (SHOW JOBS)\nSELECT\n  job_id,\n  job_type,\n  description,\n  created,\n  now() - created AS waiting_for,\n  running_status\nFROM j\nWHERE status = 'running'\n  AND job_type = 'SCHEMA CHANGE GC'\n  AND running_status LIKE '%waiting for MVCC GC%'\nORDER BY created\nLIMIT 50;\n```\n\n**Interpretation:**\n- **Normal:** SCHEMA CHANGE GC jobs wait for data to become garbage-collectable based on the zone-level `gc.ttlseconds` (default 4 hours)\n- **Expected duration:** Up to `gc.ttlseconds` + some overhead\n- **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)\n\n**Why this happens:**\nAfter 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.\n\nSee [job states reference](references/job-states.md) for detailed MVCC GC explanation.\n\n### Query 5: Automatic Job Health (24h Window)\n\nMonitor automatic background jobs like statistics collection:\n\n```sql\n-- Automatic jobs in last 24 hours\nSELECT\n  job_id,\n  job_type,\n  description,\n  status,\n  created,\n  finished,\n  COALESCE(finished, now()) - created AS duration\nFROM [SHOW AUTOMATIC JOBS]\nWHERE created > now() - INTERVAL '24 hours'\n  AND job_type IN ('AUTO CREATE STATS', 'AUTO SQL STATS COMPACTION')\nORDER BY created DESC\nLIMIT 50;\n```\n\n**Key job types:**\n- `AUTO CREATE STATS`: Automatic table statistics refresh (critical for query optimizer)\n- `AUTO SQL STATS COMPACTION`: Periodic cleanup of statement/transaction statistics tables\n\n**Health indicators:**\n- **Healthy:** Regular successful executions (every few hours)\n- **Unhealthy:** No recent executions, or high failure rate\n- **Impact of failure:** Stale statistics lead to poor query plans and slow queries\n\n### Query 6: Jobs by Type and Status\n\nAggregated view for pattern analysis:\n\n```sql\n-- Job distribution by type and status (last 24h)\nWITH j AS (SHOW JOBS)\nSELECT\n  job_type,\n  status,\n  COUNT(*) AS job_count,\n  MIN(created) AS oldest,\n  MAX(created) AS newest\nFROM j\nWHERE created > now() - INTERVAL '24 hours'\nGROUP BY job_type, status\nORDER BY job_type, status;\n```\n\n**Use case:**\n- Identify patterns (e.g., all BACKUP jobs failing, multiple schema changes stuck)\n- Spot anomalies (e.g., unusual job type volume)\n- Track job success rates by type\n\n### Query 7: Backup and Restore Progress\n\nTrack progress of backup/restore jobs:\n\n```sql\n-- Active backup/restore jobs with progress\nWITH j AS (SHOW JOBS)\nSELECT\n  job_id,\n  job_type,\n  description,\n  created,\n  now() - created AS running_for,\n  ROUND(COALESCE(fraction_completed, 0) * 100, 2) AS percent_complete,\n  CASE\n    WHEN fraction_completed > 0 AND fraction_completed < 1 THEN\n      ((now() - created) / fraction_completed) - (now() - created)\n    ELSE NULL\n  END AS estimated_time_remaining,\n  running_status\nFROM j\nWHERE status = 'running'\n  AND job_type IN ('BACKUP', 'RESTORE')\nORDER BY created\nLIMIT 50;\n```\n\n**Key columns:**\n- `percent_complete`: Progress percentage (0-100)\n- `estimated_time_remaining`: Rough estimate based on current progress rate\n- `running_status`: Detailed status (e.g., \"performing backup to s3://...\")\n\n**Note:** `fraction_completed` may be NULL for some job types or early in execution.\n\n## Common Workflows\n\n### Workflow 1: Schema Change Stuck Investigation\n\n**Scenario:** User reports ALTER TABLE or CREATE INDEX appears stuck.\n\n1. **Check for running schema changes:**\n   ```sql\n   WITH j AS (SHOW JOBS)\n   SELECT job_id, description, created, now() - created AS running_for,\n          fraction_completed, running_status\n   FROM j\n   WHERE status = 'running'\n     AND job_type IN ('SCHEMA CHANGE', 'NEW SCHEMA CHANGE')\n   ORDER BY created;\n   ```\n\n2. **Identify MVCC GC waits:**\n   ```sql\n   -- Use Query 4 to find \"waiting for MVCC GC\" jobs\n   ```\n\n3. **Interpret results:**\n   - If `running_status` = \"waiting for MVCC GC\": Normal for post-DROP cleanup (wait up to `gc.ttlseconds`)\n   - If long-running with low `fraction_completed`: Check for contention, large table size, or resource constraints\n   - If failed: Check `error` column for specific failure reason\n\n4. **Next steps:**\n   - MVCC GC wait: Verify the effective `gc.ttlseconds` with `SHOW ZONE CONFIGURATION FOR TABLE/DATABASE/RANGE ...` against the affected object and wait\n   - Resource constraints: Check cluster CPU/memory usage\n   - Failed job: Address error (permissions, constraints) and retry operation\n\n### Workflow 2: Failed Backup Triage\n\n**Scenario:** Scheduled backup job failed.\n\n1. **Find recent failed backups:**\n   ```sql\n   -- Use Query 1 filtered for BACKUP job type\n   WITH j AS (SHOW JOBS)\n   SELECT job_id, description, created, finished, error\n   FROM j\n   WHERE status = 'failed'\n     AND job_type = 'BACKUP'\n     AND created > now() - INTERVAL '24 hours'\n   ORDER BY created DESC;\n   ```\n\n2. **Analyze error messages:**\n   - \"permission denied\": Check external storage credentials\n   - \"timeout\": Network connectivity to backup destination\n   - \"no space left\": Destination storage full\n   - \"connection refused\": External storage endpoint unreachable\n\n3. **Verify backup destination:**\n   ```sql\n   -- Check SHOW BACKUP for successful backups to same destination\n   SHOW BACKUP 's3://bucket/path';\n   ```\n\n4. **Remediate and retry:**\n   - Fix underlying issue (credentials, storage, network)\n   - Re-run backup command\n   - Monitor with Query 7 for progress\n\n### Workflow 3: Automatic Job Health Check\n\n**Scenario:** Proactive monitoring of automatic background jobs.\n\n1. **Check AUTO CREATE STATS frequency:**\n   ```sql\n   -- Use Query 5 to see recent automatic statistics jobs\n   SELECT job_type, status, COUNT(*) AS job_count,\n          MAX(created) AS most_recent\n   FROM [SHOW AUTOMATIC JOBS]\n   WHERE created > now() - INTERVAL '24 hours'\n     AND job_type = 'AUTO CREATE STATS'\n   GROUP BY job_type, status;\n   ```\n\n2. **Expected pattern:**\n   - Multiple successful AUTO CREATE STATS jobs per day (depends on table update frequency)\n   - Regular AUTO SQL STATS COMPACTION (typically once per hour)\n\n3. **Warning signs:**\n   - No AUTO CREATE STATS in last 24h: Statistics collection may be disabled\n   - High failure rate: Check cluster resource constraints or permission issues\n   - No AUTO SQL STATS COMPACTION: Stats table may grow unbounded\n\n4. **Verify settings:**\n   ```sql\n   SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should be true\n   SHOW CLUSTER SETTING sql.stats.automatic_collection.min_stale_rows;\n   ```\n\n### Workflow 4: Long-Running Job Monitoring\n\n**Scenario:** Track progress of expected long-running operations.\n\n1. **Identify long-running jobs:**\n   ```sql\n   -- Use Query 2 with custom threshold\n   WITH j AS (SHOW JOBS)\n   SELECT job_id, job_type, description,\n          now() - created AS running_for,\n          fraction_completed\n   FROM j\n   WHERE status = 'running'\n     AND created < now() - INTERVAL '30 minutes'\n   ORDER BY created;\n   ```\n\n2. **Monitor progress over time:**\n   ```sql\n   -- Re-run every 10-15 minutes, track fraction_completed changes\n   -- Example: 0.25 → 0.40 → 0.55 indicates steady progress\n   ```\n\n3. **Estimate completion:**\n   ```sql\n   -- Use Query 7 for backup/restore jobs with time estimates\n   ```\n\n4. **Decide on action:**\n   - Steady progress: Continue monitoring\n   - Stalled progress (fraction_completed not increasing): Investigate with [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md)\n   - Failed: Use Query 1 to check error\n\n## Safety Considerations\n\n**Read-only operations (all diagnostic queries):**\nAll `SHOW JOBS` and `SHOW AUTOMATIC JOBS` queries are read-only and safe to run in production. No performance impact on cluster operations.\n\n**Job control operations (opt-in):**\n\n**CAUTION: Pausing or canceling jobs can have data integrity implications**\n\nOnly proceed with job control if:\n- You have `CONTROLJOB` role option\n- You understand the implications (e.g., canceling a schema change mid-execution may require manual cleanup)\n- You have authorization to interrupt cluster operations\n- You've verified the job is truly problematic (not just slow)\n\n**Job control commands:**\n```sql\n-- Pause a running job (can be resumed later)\nPAUSE JOB <job_id>;\n\n-- Resume a paused job\nRESUME JOB <job_id>;\n\n-- Cancel a job (terminal - cannot be resumed)\nCANCEL JOB <job_id>;\n```\n\n**Risks by job type:**\n- **SCHEMA CHANGE:** Canceling may leave schema in inconsistent state; prefer PAUSE and investigation\n- **BACKUP:** Canceling is safe (can retry); pausing is better for temporary issues\n- **RESTORE:** Canceling may leave database partially restored; requires cleanup\n- **AUTO CREATE STATS:** Canceling is safe (will retry later automatically)\n\n**Best practice:** Focus on monitoring and diagnosis; only use control operations when explicitly required and authorized.\n\nSee [permissions reference](references/permissions.md) for CONTROLJOB role option setup.\n\n## Troubleshooting\n\n| Issue | Cause | Fix |\n|-------|-------|-----|\n| `SHOW JOBS` returns empty | No jobs in last 12h, or insufficient privileges | Grant `VIEWJOB` privilege; verify cluster has recent job activity |\n| \"waiting for MVCC GC\" for many hours | Normal behavior for SCHEMA CHANGE GC after DROP operations | Wait up to `gc.ttlseconds` (default 4h); check the effective value with `SHOW ZONE CONFIGURATION FOR ...` against the affected table/database/range |\n| Can't pause/resume job: \"permission denied\" | Missing `CONTROLJOB` role option | Use `ALTER ROLE <username> WITH CONTROLJOB` (not GRANT SYSTEM) |\n| Job stuck at same `fraction_completed` | Job may be processing large batch, or actually stuck | Wait 15-30 min and re-check; if no change, investigate with live query triage |\n| No AUTO CREATE STATS jobs | Automatic collection disabled | Check `sql.stats.automatic_collection.enabled = true` |\n| `SHOW AUTOMATIC JOBS` shows old jobs only | Need to filter by time window | Add `WHERE created > now() - INTERVAL '24 hours'` |\n| Failed job with \"schema change GC\" error | Expected for post-DROP cleanup failures | Usually safe to ignore; job will retry automatically |\n| Job error: \"concurrent schema change\" | Multiple schema changes on same table | Wait for first schema change to complete, then retry |\n\n## Key Considerations\n\n- **MVCC GC waiting:** Normal for post-DROP cleanup; duration tied to the zone-level `gc.ttlseconds` (default 4h)\n- **Automatic job health:** Regular AUTO CREATE STATS is critical for query optimizer performance\n- **Job control:** PAUSE is safer than CANCEL; some cancellations require manual cleanup\n- **Progress:** `fraction_completed` may be NULL for some job types\n\n## References\n\n**Skill references:**\n- [RBAC and privilege setup](references/permissions.md)\n- [Job states and transitions](references/job-states.md)\n- [Job types catalog](references/job-types.md)\n- [SQL query variations](references/sql-query-variations.md)\n\n**Related skills:**\n- [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) - For live query monitoring (job-executed statements may not appear)\n- [profiling-statement-fingerprints](../profile-stmt-fingerprints/SKILL.md) - For historical query analysis (background jobs excluded)\n\n**Official CockroachDB Documentation:**\n- [SHOW JOBS](https://www.cockroachlabs.com/docs/stable/show-jobs.html)\n- [SHOW AUTOMATIC JOBS](https://www.cockroachlabs.com/docs/stable/show-automatic-jobs.html)\n- [PAUSE JOB](https://www.cockroachlabs.com/docs/stable/pause-job.html)\n- [RESUME JOB](https://www.cockroachlabs.com/docs/stable/resume-job.html)\n- [CANCEL JOB](https://www.cockroachlabs.com/docs/stable/cancel-job.html)\n- [crdb_internal](https://www.cockroachlabs.com/docs/stable/crdb-internal.html)\n- [Jobs Page (DB Console)](https://www.cockroachlabs.com/docs/stable/ui-jobs-page.html)\n- [VIEWJOB privilege](https://www.cockroachlabs.com/docs/stable/security-reference/authorization.html#supported-privileges)\n"
}