monitoring-background-jobs

name: monitoring-background-jobs 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. compatibility: Requires SQL access with VIEWJOB privilege or CONTROLJOB role option for cluster-wide visibility. Uses SHOW JOBS and SHOW AUTOMATIC JOBS (production-safe). metadata: author: cockroachdb version: "1.0"

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 for live queries, profiling-statement-fingerprints 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 and job states reference for complete catalogs.

Core Diagnostic Queries

Query 1: Failed Jobs (Last 12 Hours)

Identify jobs that failed with error messages:

-- 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:

-- 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:

-- 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:

-- 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 for detailed MVCC GC explanation.

Query 5: Automatic Job Health (24h Window)

Monitor automatic background jobs like statistics collection:

-- 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:

-- 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:

-- 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:

   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:

   -- 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/RANGE ... against the affected object and wait
   • Resource constraints: Check cluster CPU/memory usage
   • Failed job: Address error (permissions, constraints) and retry operation

Workflow 2: Failed Backup Triage

Scenario: Scheduled backup job failed.

1. Find recent failed backups:

   -- Use Query 1 filtered for BACKUP job type
   WITH j AS (SHOW JOBS)
   SELECT job_id, description, created, finished, error
   FROM j
   WHERE status = 'failed'
     AND job_type = 'BACKUP'
     AND created > now() - INTERVAL '24 hours'
   ORDER BY created DESC;
   

2. Analyze error messages:

   • "permission denied": Check external storage credentials
   • "timeout": Network connectivity to backup destination
   • "no space left": Destination storage full
   • "connection refused": External storage endpoint unreachable

3. Verify backup destination:

   -- Check SHOW BACKUP for successful backups to same destination
   SHOW BACKUP 's3://bucket/path';
   

4. Remediate and retry:

   • Fix underlying issue (credentials, storage, network)
   • Re-run backup command
   • Monitor with Query 7 for progress

Workflow 3: Automatic Job Health Check

Scenario: Proactive monitoring of automatic background jobs.

1. Check AUTO CREATE STATS frequency:

   -- Use Query 5 to see recent automatic statistics jobs
   SELECT job_type, status, COUNT(*) AS job_count,
          MAX(created) AS most_recent
   FROM [SHOW AUTOMATIC JOBS]
   WHERE created > now() - INTERVAL '24 hours'
     AND job_type = 'AUTO CREATE STATS'
   GROUP BY job_type, status;
   

2. Expected pattern:

   • Multiple successful AUTO CREATE STATS jobs per day (depends on table update frequency)
   • Regular AUTO SQL STATS COMPACTION (typically once per hour)

3. Warning signs:

   • No AUTO CREATE STATS in last 24h: Statistics collection may be disabled
   • High failure rate: Check cluster resource constraints or permission issues
   • No AUTO SQL STATS COMPACTION: Stats table may grow unbounded

4. Verify settings:

   SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should be true
   SHOW CLUSTER SETTING sql.stats.automatic_collection.min_stale_rows;
   

Workflow 4: Long-Running Job Monitoring

Scenario: Track progress of expected long-running operations.

1. Identify long-running jobs:

   -- Use Query 2 with custom threshold
   WITH j AS (SHOW JOBS)
   SELECT job_id, job_type, description,
          now() - created AS running_for,
          fraction_completed
   FROM j
   WHERE status = 'running'
     AND created < now() - INTERVAL '30 minutes'
   ORDER BY created;
   

2. Monitor progress over time:

   -- Re-run every 10-15 minutes, track fraction_completed changes
   -- Example: 0.25 → 0.40 → 0.55 indicates steady progress
   

3. Estimate completion:

   -- Use Query 7 for backup/restore jobs with time estimates
   

4. Decide on action:

   • Steady progress: Continue monitoring
   • Stalled progress (fraction_completed not increasing): Investigate with triaging-live-sql-activity
   • Failed: Use Query 1 to check error

Safety Considerations

Read-only operations (all diagnostic queries): All SHOW JOBS and SHOW AUTOMATIC JOBS queries are read-only and safe to run in production. No performance impact on cluster operations.

Job control operations (opt-in):

CAUTION: Pausing or canceling jobs can have data integrity implications

Only proceed with job control if:

• You have CONTROLJOB role option
• You understand the implications (e.g., canceling a schema change mid-execution may require manual cleanup)
• You have authorization to interrupt cluster operations
• You've verified the job is truly problematic (not just slow)

Job control commands:

-- Pause a running job (can be resumed later)
PAUSE JOB <job_id>;

-- Resume a paused job
RESUME JOB <job_id>;

-- Cancel a job (terminal - cannot be resumed)
CANCEL JOB <job_id>;

Risks by job type:

• SCHEMA CHANGE: Canceling may leave schema in inconsistent state; prefer PAUSE and investigation
• BACKUP: Canceling is safe (can retry); pausing is better for temporary issues
• RESTORE: Canceling may leave database partially restored; requires cleanup
• AUTO CREATE STATS: Canceling is safe (will retry later automatically)

Best practice: Focus on monitoring and diagnosis; only use control operations when explicitly required and authorized.

See permissions reference for CONTROLJOB role option setup.

Troubleshooting

Key Considerations

• MVCC GC waiting: Normal for post-DROP cleanup; duration tied to the zone-level gc.ttlseconds (default 4h)
• Automatic job health: Regular AUTO CREATE STATS is critical for query optimizer performance
• Job control: PAUSE is safer than CANCEL; some cancellations require manual cleanup
• Progress: fraction_completed may be NULL for some job types

References

Skill references:

• RBAC and privilege setup
• Job states and transitions
• Job types catalog
• SQL query variations

Related skills:

• triaging-live-sql-activity - For live query monitoring (job-executed statements may not appear)
• profiling-statement-fingerprints - For historical query analysis (background jobs excluded)

Official CockroachDB Documentation:

• SHOW JOBS
• SHOW AUTOMATIC JOBS
• PAUSE JOB
• RESUME JOB
• CANCEL JOB
• crdb_internal
• Jobs Page (DB Console)
• VIEWJOB privilege