monitoring-background-jobs

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