Schedule SQL Checks at Custom Intervals: Tools and Examples

Published Dec 19, 2025

Learn how to schedule SQL checks at custom intervals with cron, pg_cron, MySQL events, and more—plus sample scripts, alerts, and best practices.

Schedule SQL Checks at Custom Intervals: Tools and Examples

Proactive data monitoring starts with well-timed checks. Whether you’re watching for late ETL loads, threshold breaches, or anomalous row growth, the ability to schedule SQL checks at custom intervals helps you catch issues before they hit users. This guide shows you practical ways to run read-only checks across popular environments—Linux cron, PostgreSQL pg_cron, MySQL Event Scheduler, SQL Server Agent, Kubernetes CronJobs, and cloud schedulers—plus examples for alerting via email and chat. You’ll get working snippets, a comparison of schedulers, and battle-tested best practices for performance, security, and noise reduction.

Choosing the Right Scheduler

There are many ways to automate queries. Your best option depends on who owns the runtime, how centralized you want observability, and what kind of SLAs you have. Below is a quick comparison to help you pick a path.

Scheduler Works Where Best For Pros Cons Interval Syntax
cron Linux/Unix hosts Simple, host-level jobs Ubiquitous, lightweight Host-coupled, minimal visibility */7 * * * * (every 7 minutes)
systemd timers Modern Linux Service-managed jobs Logging, dependencies Host-coupled OnCalendar=*:0/7
PostgreSQL pg_cron Inside Postgres DB-local checks Runs close to data DB superuser needed to install Cron syntax
MySQL Event Scheduler Inside MySQL DB-local checks No external host needed Limited alert hooks EVERY 5 MINUTE
SQL Server Agent SQL Server Enterprise Windows/VMs Rich scheduling, alerts Agent required GUI or T-SQL schedules
Kubernetes CronJob K8s clusters Containerized checks Scales, isolated runtime K8s complexity Cron syntax
Cloud schedulers AWS, GCP, Azure Serverless jobs Managed, observable Vendor tie-in Cron or rate-based

Common Patterns for SQL Checks

Before wiring up timers, define what you’re checking and how you’ll alert. Popular patterns include:

  • Freshness checks: Verify that a table was updated recently (e.g., within 30 minutes of expected ETL completion).
  • Threshold checks: Alert when counts, sums, or averages exceed or fall below defined limits.
  • Anomaly signals: Compare today’s metrics to historical baselines (e.g., 3σ from the trailing 7-day mean).
  • Operational health: Monitor replication lag, deadlocks, or long-running queries.

Keep checks read-only and cheap: use indexes, limit scanned columns, and set timeouts so monitoring never becomes the outage.

Implementations by Environment

Bash + cron (PostgreSQL example)

On any Linux host, you can schedule SQL checks at custom intervals with cron. The script below checks whether orders are more than 15 minutes late to be processed and sends an email only when the condition is true.

#!/usr/bin/env bash
set -euo pipefail

# Env vars should be provided by a secure source, e.g., /etc/default/checks or a secrets manager
: "${PGHOST:?}" "${PGUSER:?}" "${PGDATABASE:?}" "${PGPASSWORD:?}"

SQL="""
SELECT count(*) AS late
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '15 minutes';
"""

late_count=$(psql --no-align --tuples-only -c "$SQL" 2>/dev/null || echo "ERR")

if [[ "$late_count" == "ERR" ]]; then
  echo "[orders_late_check] query failed on $(date -Iseconds)" | mail -s "SQL check error" ops@example.com
  exit 1
fi

if (( late_count > 0 )); then
  echo "Late orders: $late_count at $(date -Iseconds)" | mail -s "Late orders alert" ops@example.com
fi

Crontab entries control custom intervals. For example, to run every 7 minutes:

*/7 * * * * /opt/checks/orders_late.sh >> /var/log/checks.log 2>&1

Other examples: every 17 minutes (*/17 * * * *), every weekday at 09:05 (5 9 * * 1-5), or a quick “cooldown” strategy by staggering different checks across minutes 0, 2, 4, etc. to spread load.

Python + Slack webhook (cron or Task Scheduler)

If you want richer logic and chat alerts, use Python with a webhook. This example queries PostgreSQL and posts to Slack only when a threshold is crossed.

import os, requests, psycopg2

conn = psycopg2.connect(os.environ["DATABASE_URL"])  # e.g., postgresql://user:pass@host/db
conn.set_session(readonly=True, autocommit=True)

with conn.cursor() as cur:
    cur.execute("""
        SELECT AVG(total_amount) AS avg_order
        FROM orders
        WHERE created_at > now() - interval '10 minutes';
    """)
    avg_order = cur.fetchone()[0] or 0

threshold = float(os.getenv("AVG_ORDER_THRESHOLD", "250"))
if avg_order > threshold:
    msg = {
        "text": f"Avg order in last 10m is {avg_order:.2f} (threshold {threshold})."
    }
    requests.post(os.environ["SLACK_WEBHOOK_URL"], json=msg, timeout=5)

Schedule with cron on Linux or Task Scheduler on Windows. Keep secrets out of code (environment variables, vaults, or OS keychains).

PostgreSQL native: pg_cron

pg_cron runs inside Postgres, which can simplify network and credential management. Install and enable it via your distribution or cloud provider.

-- In postgresql.conf, ensure shared_preload_libraries includes 'pg_cron'
-- shared_preload_libraries = 'pg_cron'
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Example: schedule a freshness check every 5 minutes
-- Note: Some versions use cron.schedule(job_name text, schedule text, command text)
SELECT cron.schedule(
  'orders_freshness_check',
  '*/5 * * * *',
  $$
    INSERT INTO checks_log(job, happened_at, passed)
    SELECT 'orders_freshness_check', now(),
           (EXISTS (
               SELECT 1 FROM orders
               WHERE updated_at > now() - interval '15 minutes'
           ));
  $$
);

With pg_cron, you can run SQL directly or call stored procedures. Ensure your jobs run with a dedicated, least-privilege role and set per-job timeouts to avoid runaway tasks.

MySQL native: Event Scheduler

MySQL’s Event Scheduler can execute SQL on a repeating schedule. First, enable it and then create an event.

-- Enable globally (or via my.cnf):
SET GLOBAL event_scheduler = ON;

-- Create a check that runs every 5 minutes
CREATE TABLE IF NOT EXISTS alert_log (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  alert_name VARCHAR(100),
  happened_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  payload JSON
);

DELIMITER //
CREATE EVENT IF NOT EXISTS check_stale_sessions
ON SCHEDULE EVERY 5 MINUTE
DO BEGIN
  INSERT INTO alert_log(alert_name, payload)
  SELECT 'stale_sessions', JSON_OBJECT('count', COUNT(*))
  FROM sessions
  WHERE last_seen < NOW() - INTERVAL 30 MINUTE;
END //
DELIMITER ;

To actually notify, pair the event with a lightweight notifier process that polls alert_log and forwards high-signal entries to email or chat.

SQL Server: Agent Job

SQL Server Agent supports sophisticated schedules and notifications. Here’s a minimal T-SQL example that runs a check every 10 minutes.

EXEC msdb.dbo.sp_add_job @job_name = N'Orders Late Check';
EXEC msdb.dbo.sp_add_jobstep
  @job_name = N'Orders Late Check',
  @step_name = N'Check Late Orders',
  @subsystem = N'TSQL',
  @command = N'
    IF EXISTS (
      SELECT 1 FROM dbo.Orders WHERE Status = ''Pending''
      AND DATEDIFF(MINUTE, CreatedAt, GETUTCDATE()) > 15
    )
    BEGIN
      -- Raise error for alerting or write to a log table
      RAISERROR(''Late orders pending'', 10, 1) WITH NOWAIT;
    END';
EXEC msdb.dbo.sp_add_schedule
  @schedule_name = N'Every 10 Minutes',
  @freq_type = 4,            -- daily
  @freq_interval = 1,
  @freq_subday_type = 4,     -- minutes
  @freq_subday_interval = 10,
  @active_start_time = 000000;
EXEC msdb.dbo.sp_attach_schedule @job_name = N'Orders Late Check', @schedule_name = N'Every 10 Minutes';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Orders Late Check';

Use Database Mail or Alert rules to route notifications to email or Teams.

Kubernetes CronJob

In containerized environments, a CronJob isolates dependencies and scales easily.

apiVersion: batch/v1
kind: CronJob
metadata:
  name: orders-late-check
spec:
  schedule: "*/7 * * * *"  # every 7 minutes
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: checker
              image: ghcr.io/yourorg/sql-checker:latest
              envFrom:
                - secretRef:
                    name: db-credentials
              args: ["python", "/app/check_orders.py"]
          restartPolicy: OnFailure

Store credentials in Kubernetes Secrets and rotate regularly. Emit metrics and logs to your observability stack (e.g., Prometheus and Loki).

Alerting Channels and Deduplication

Once you schedule SQL checks at custom intervals, the next challenge is alert delivery without noise. Consider:

  • Email: Simple to set up; add subject prefixes and RFC-compliant formatting for inbox rules.
  • Chat (Slack/Teams): Webhooks are easy; thread or tag alerts to reduce channel clutter.
  • Webhooks: Universal; send JSON payloads to a central alerting service that handles routing and escalation.
  • SMS/Push: Reserve for high-severity alerts or on-call rotations.

Implement deduplication keys (e.g., alert_name + dimension + severity) and cooldown windows so repeating failures don’t cause floods. Auto-resolve messages can close the loop when conditions recover.

Best Practices for Safe and Efficient Checks

  • Least privilege: Use a dedicated read-only DB role with access to only the schemas/tables your checks need.
  • Timeouts everywhere: Apply statement_timeout (Postgres), max_execution_time (MySQL), or command-level timeouts in your client.
  • Index-assisted filters: Ensure WHERE clauses hit indexed columns; avoid full table scans on hot tables.
  • Idempotent alerts: Write to a check_runs table with a unique key per interval to prevent duplicate processing.
  • Sampling vs. exactness: For frequent checks, consider approximate methods (e.g., hyperloglog, sample windows) to reduce load.
  • Staggered schedules: Spread checks to avoid stampedes at the top of the minute.
  • Config as code: Keep queries and thresholds in version control; review and test via CI.

Observability and Governance

Monitoring the monitors is vital. Log every run’s start, end, latency, and outcome. A minimal schema works across engines:

-- Generic idea, adapt types to your DB
CREATE TABLE check_runs (
  id BIGSERIAL PRIMARY KEY,
  check_name TEXT NOT NULL,
  scheduled_at TIMESTAMPTZ NOT NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at TIMESTAMPTZ,
  status TEXT CHECK (status IN ('ok','alert','error')),
  details JSONB
);

Emit metrics like success ratio, average latency, and alert rate to your APM. Apply naming conventions (domain_entity_condition) and ensure ownership is clear (Slack group or on-call rotation). For secrets, use cloud KMS, Vault, or OS keychains—never store plain passwords in scripts or CronJobs.

Troubleshooting Tips

  • Timezone drift: Cron, database servers, and alert consumers must agree on timezones. Use UTC in code and convert on display.
  • Daylight saving: Cron-based schedules can shift during DST. Prefer UTC or use schedulers that understand calendars.
  • Lock contention: Use READ COMMITTED and avoid locking rows unnecessarily. Consider NOWAIT/SKIP LOCKED where appropriate.
  • Slow checks: EXPLAIN/EXPLAIN ANALYZE your queries and add missing indexes; consider materialized views refreshed less frequently.
  • Network hiccups: Add retries with backoff for transient errors, but cap attempts to avoid duplicate alerts.
  • Permissions: If installation of pg_cron or Events isn’t allowed, run checks from an app host or serverless job with read-only credentials.

Putting It All Together

Effective data monitoring is about reliability, not just frequency. Pick a scheduler that fits your runtime model, write fast read-only checks, and deploy alerting that’s concise, deduplicated, and actionable. Start with one or two high-value checks (e.g., freshness and threshold), measure their signal-to-noise ratio, then expand coverage thoughtfully. As your estate grows, centralize logs, metrics, and configuration so you can reason about trends and quickly remediate issues.

If you prefer a lightweight mobile companion that can connect to PostgreSQL, MongoDB, and MySQL, transform queries into alerts, and run checks on flexible schedules with push or email notifications, consider trying Query Alert.

Key takeaway: choose the simplest scheduler that meets your requirements, keep checks cheap, and iterate on thresholds and deduplication so the right people get the right alerts at the right time.