SQL Server tempdb Space and Growth Monitor

id: mssql-tempdb-growth-monitor
namespace: company.team
description: |
  Watch SQL Server tempdb space consumption and growth rate so an instance is
  never taken down by a full tempdb. An hourly schedule that ships disabled
  plus a keyed webhook sample file usage with sqlcmd, falling back to a bundled
  fixture when the instance is unreachable, then a python step aggregates the
  used percentage, growth in MB per hour, and a verdict. Above the configured
  threshold the flow alerts Slack, opens a GitHub issue with the per-file
  breakdown, and records one recommendation per hot file, otherwise it logs the
  all-clear, and the verdict and metrics are published as outputs.

triggers:
  - id: hourly_tempdb_watch
    type: io.kestra.plugin.core.trigger.Schedule
    description: Hourly space watch at the top of every hour, shipped disabled until
      an operator switches it on.
    cron: "0 * * * *"
    disabled: true
  - id: on_demand_watch
    type: io.kestra.plugin.core.trigger.Webhook
    description: POST here to sample tempdb on a named instance, for example after a
      release or when a database alert fires.
    key: "{{ secret('TEMPDB_WEBHOOK_KEY') }}"

inputs:
  - id: instance_name
    type: STRING
    defaults: sql-prod-01.database.windows.net
    description: Endpoint sqlcmd connects to for the tempdb sample, also used in the
      alert text and the issue title.
  - id: usage_threshold_pct
    type: INT
    defaults: 85
    description: Percentage of total tempdb space used that sends the run to Slack
      and to a GitHub issue.

tasks:
  # --- 1. sample and aggregate ---
  - id: usage_workspace
    type: io.kestra.plugin.core.flow.WorkingDirectory
    description: One shared directory so the sqlcmd sample, the aggregator, and the
      per-file recommendations all see the same files.
    tasks:
      - id: capture_tempdb_usage
        type: io.kestra.plugin.scripts.shell.Commands
        description: Write fixture usage and growth rows with heredocs, then run a best
          effort sqlcmd so a reachable instance overrides the fixture.
        taskRunner:
          type: io.kestra.plugin.scripts.runner.docker.Docker
          entryPoint: []
        containerImage: mcr.microsoft.com/mssql-tools
        env:
          INSTANCE_NAME: "{{ inputs.instance_name }}"
          MSSQL_USER: "{{ secret('MSSQL_USER') }}"
          MSSQL_PASSWORD: "{{ secret('MSSQL_PASSWORD') }}"
        commands:
          - |
            set -u
            cat > tempdb-usage.tsv <<'TSV'
            file_name	type_desc	current_size_mb	used_mb
            tempdb1.ndf	DATA	4096	3900
            tempdb2.ndf	DATA	4096	3850
            tempdb3.ndf	DATA	4096	4000
            tempdb4.ndf	DATA	4096	2500
            tempdb5.ndf	DATA	4096	3950
            tempdb6.ndf	DATA	4096	3880
            templog1.ldf	LOG	1024	980
            templog2.ldf	LOG	1024	700
            TSV

            cat > tempdb-growth.tsv <<'TSV'
            event_time	growth_mb
            2026-01-01T00:00:00	256
            2026-01-01T02:00:00	512
            2026-01-01T04:00:00	128
            2026-01-01T06:00:00	640
            TSV

            echo "sampling tempdb files on ${INSTANCE_NAME}"
            sqlcmd -S "${INSTANCE_NAME}" -U "${MSSQL_USER}" -P "${MSSQL_PASSWORD}" -W -s '|' -o live-tempdb-usage.tsv -Q "SELECT df.name AS file_name, df.type_desc AS type_desc, CAST(df.size * 8 / 1024 AS INT) AS current_size_mb, CAST(FILEPROPERTY(df.name, 'SpaceUsed') * 8 / 1024 AS INT) AS used_mb FROM tempdb.sys.database_files ORDER BY df.type_desc, df.name" || true

            echo "best effort sqlcmd complete for ${INSTANCE_NAME}"

      - id: aggregate_usage
        type: io.kestra.plugin.scripts.python.Script
        description: Parse the sample, compute used percentage, growth in MB per hour,
          the hot file list, and the verdict, then emit them through the outputs
          protocol.
        taskRunner:
          type: io.kestra.plugin.scripts.runner.docker.Docker
        containerImage: python:3.12-slim
        env:
          INSTANCE_NAME: "{{ inputs.instance_name }}"
          USAGE_THRESHOLD: "{{ inputs.usage_threshold_pct }}"
        script: |
          import json
          import os
          import re
          from datetime import datetime

          LIVE_FILE = "live-tempdb-usage.tsv"
          FIXTURE_FILE = "tempdb-usage.tsv"
          GROWTH_FILE = "tempdb-growth.tsv"
          HOT_PCT = 80.0

          def to_float(value):
              try:
                  return float(str(value).strip())
              except (TypeError, ValueError):
                  return None

          def fields_of(line, count):
              for sep in ("\t", "|"):
                  parts = [f.strip() for f in line.split(sep)]
                  if len(parts) >= count:
                      return parts
              return [f.strip() for f in re.split(r"\s{2,}", line.strip())]

          def parse_usage(path):
              rows = []
              if not os.path.isfile(path) or os.path.getsize(path) == 0:
                  return rows
              with open(path, "r", errors="replace") as handle:
                  for line in handle:
                      parts = fields_of(line.strip(), 4)
                      if len(parts) < 4:
                          continue
                      size, used = to_float(parts[2]), to_float(parts[3])
                      if size is None or used is None or size <= 0 or used < 0:
                          continue
                      rows.append({"name": parts[0],
                                   "type": "LOG" if "LOG" in parts[1].upper() else "DATA",
                                   "current_size_mb": size, "used_mb": used})
              return rows

          def parse_growth(path):
              events = []
              if not os.path.isfile(path) or os.path.getsize(path) == 0:
                  return events
              with open(path, "r", errors="replace") as handle:
                  for line in handle:
                      parts = [f.strip() for f in line.strip().split("\t")]
                      if len(parts) < 2:
                          parts = fields_of(line.strip(), 2)
                      amount = to_float(parts[1]) if len(parts) >= 2 else None
                      if amount is None or amount < 0:
                          continue
                      stamp = None
                      for fmt in ("%Y-%m-%dT%H:%M:%S", "%Y-%m-%d %H:%M:%S", "%Y-%m-%d"):
                          try:
                              stamp = datetime.strptime(parts[0], fmt)
                              break
                          except ValueError:
                              pass
                      events.append({"stamp": stamp, "growth_mb": amount})
              return events

          rows = parse_usage(LIVE_FILE)
          source = "live" if rows else ""
          if not rows:
              rows = parse_usage(FIXTURE_FILE)
              source = "fixture" if rows else "none"
          events = parse_growth(GROWTH_FILE)
          try:
              threshold = float(os.environ.get("USAGE_THRESHOLD", ""))
          except ValueError:
              threshold = 85.0
          instance = os.environ.get("INSTANCE_NAME", "") or "unknown-instance"
          total_size = sum(r["current_size_mb"] for r in rows)
          total_used = sum(r["used_mb"] for r in rows)
          used_pct = round(total_used / total_size * 100.0, 2) if total_size > 0 else 0.0
          stamps = [e["stamp"] for e in events if e["stamp"] is not None]
          span = (max(stamps) - min(stamps)).total_seconds() / 3600.0 if len(stamps) >= 2 else 0.0
          growth_total = sum(e["growth_mb"] for e in events)
          if span > 0:
              growth_mb_per_hour = int(round(growth_total / span))
          else:
              growth_mb_per_hour = int(round(growth_total)) if events else 0
          breakdown, hot_files = [], []
          for row in sorted(rows, key=lambda r: -(r["used_mb"] / r["current_size_mb"])):
              pct = round(row["used_mb"] / row["current_size_mb"] * 100.0, 2)
              entry = {"name": row["name"], "type": row["type"], "used_pct": pct,
                       "current_size_mb": int(round(row["current_size_mb"])),
                       "used_mb": int(round(row["used_mb"]))}
              breakdown.append(entry)
              if pct <= HOT_PCT:
                  continue
              headroom = int(round(row["current_size_mb"] * (100.0 - pct) / 100.0))
              if row["type"] == "LOG":
                  action = "grow the log by %d MB or truncate it after log backups" % max(headroom, 64)
              else:
                  action = "grow the data file by %d MB or shrink it after the batch job and rebalance the other files" % max(headroom, 128)
              hot_files.append({"name": entry["name"], "type": entry["type"],
                                "used_pct": pct, "used_mb": entry["used_mb"],
                                "current_size_mb": entry["current_size_mb"],
                                "recommendation": "%s (%s) sits at %.2f percent full, %d of %d MB, %s" % (
                                    entry["name"], entry["type"], pct, entry["used_mb"],
                                    entry["current_size_mb"], action)})

          md = ["| file | type | used_mb | current_size_mb | used_pct |", "| --- | --- | --- | --- | --- |"]
          md += ["| %s | %s | %d | %d | %.2f percent |" % (e["name"], e["type"], e["used_mb"],
                 e["current_size_mb"], e["used_pct"]) for e in breakdown]
          breakdown_md = "\n".join(md)
          recommendations_md = "\n".join("- " + h["recommendation"] for h in hot_files)
          if not hot_files:
              recommendations_md = "No tempdb file is above 80 percent utilization."
          payload = {"verdict": "AT_RISK" if used_pct > threshold else "HEALTHY",
                     "used_pct": used_pct, "growth_mb_per_hour": growth_mb_per_hour,
                     "total_used_mb": int(round(total_used)),
                     "total_size_mb": int(round(total_size)),
                     "file_count": len(breakdown), "hot_file_count": len(hot_files),
                     "instance_name": instance, "usage_source": source,
                     "breakdown_md": breakdown_md, "recommendations_md": recommendations_md,
                     "hot_files": hot_files}
          print("instance=%s source=%s files=%d used=%.2f percent growth=%d MB/h verdict=%s" % (
              instance, source, len(breakdown), used_pct, growth_mb_per_hour, payload["verdict"]))
          print("::" + json.dumps({"outputs": payload}) + "::")

  # --- 2. hot file recommendations ---
  - id: recommend_hot_files
    type: io.kestra.plugin.core.flow.Loop
    description: Walk the files sitting above 80 percent utilization and record one
      shrink or growth recommendation per file.
    values: "{{ outputs.aggregate_usage.vars.hot_files }}"
    concurrencyLimit: 1
    tasks:
      - id: append_recommendation
        type: io.kestra.plugin.scripts.python.Script
        description: Print the recommendation line for the current hot file and emit it
          through the outputs protocol.
        taskRunner:
          type: io.kestra.plugin.scripts.runner.docker.Docker
        containerImage: python:3.12-slim
        env:
          FILE_NAME: "{{ item.name }}"
          RECOMMENDATION: "{{ item.recommendation }}"
        script: |
          import json
          import os

          name = os.environ.get("FILE_NAME", "unknown-file")
          line = os.environ.get("RECOMMENDATION", "")
          print("recommendation for " + name + ": " + line)
          print("::" + json.dumps({"outputs": {"flagged": name, "recommendation": line}}) + "::")

  # --- 3. verdict gate ---
  - id: check_threshold
    type: io.kestra.plugin.core.flow.If
    description: Escalate only when the aggregated used percentage sits above the
      configured threshold, otherwise record the all-clear.
    condition: "{{ (outputs.aggregate_usage.vars.used_pct | number) >
      (inputs.usage_threshold_pct | number) }}"
    then:
      - id: alert_slack
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Tell the database channel the instance, the used percentage, the
          growth rate, and how many files are hot.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {
            "text": "tempdb pressure on {{ inputs.instance_name }} at {{ outputs.aggregate_usage.vars.used_pct }} percent used, {{ outputs.aggregate_usage.vars.total_used_mb }} MB in use, growing {{ outputs.aggregate_usage.vars.growth_mb_per_hour }} MB per hour, {{ outputs.aggregate_usage.vars.hot_file_count }} file(s) above 80 percent. Execution {{ execution.id }}."
          }
      - id: open_tempdb_issue
        type: io.kestra.plugin.github.issues.Create
        description: Open a labeled GitHub issue carrying the per-file breakdown and the
          recommendations so capacity work is tracked.
        jwtToken: "{{ secret('GITHUB_TOKEN') }}"
        repository: your-org/database-ops
        title: "tempdb space pressure on {{ inputs.instance_name }}"
        body: |
          ## tempdb capacity watch

          Instance **{{ inputs.instance_name }}** has used **{{ outputs.aggregate_usage.vars.used_pct }} percent** of tempdb space against a threshold of **{{ inputs.usage_threshold_pct }} percent**, growing **{{ outputs.aggregate_usage.vars.growth_mb_per_hour }} MB per hour**.

          {{ outputs.aggregate_usage.vars.breakdown_md }}

          ### Recommendations

          {{ outputs.aggregate_usage.vars.recommendations_md }}

          Execution {{ execution.id }}

          Work the file changes above, then close this issue.
        labels:
          - sqlserver
          - tempdb
    else:
      - id: log_all_clear
        type: io.kestra.plugin.core.log.Log
        description: Record the all-clear so the execution history doubles as capacity
          evidence.
        message: "tempdb healthy on {{ inputs.instance_name }} at {{
          outputs.aggregate_usage.vars.used_pct }} percent used with {{
          outputs.aggregate_usage.vars.growth_mb_per_hour }} MB per hour of
          growth."

errors:
  - id: notify_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Escalate any failure to the ops channel with the execution url.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {"text": "FAILED: {{ flow.id }} {{ execution.id }}"}

outputs:
  - id: verdict
    type: STRING
    description: HEALTHY when used percentage is at or below the threshold, AT_RISK
      when it is above.
    value: "{{ outputs.aggregate_usage.vars.verdict }}"
  - id: used_pct
    type: FLOAT
    description: Percentage of total tempdb space currently used, for example 89.24.
    value: "{{ outputs.aggregate_usage.vars.used_pct }}"
  - id: growth_mb_per_hour
    type: INT
    description: Tempdb growth in MB per hour derived from the recorded growth
      events, for example 256.
    value: "{{ outputs.aggregate_usage.vars.growth_mb_per_hour }}"
  - id: total_used_mb
    type: INT
    description: Sum of used space across every tempdb data and log file in MB, for
      example 23760.
    value: "{{ outputs.aggregate_usage.vars.total_used_mb }}"

Diagram unavailable

We could not build the topology for this blueprint. The flow itself is valid, use the YAML on the left to run it.

How it works

hourly_tempdb_watch (io.kestra.plugin.core.trigger.Schedule, cron 0 * * * *) is the fallback net and ships disabled: true, so nothing runs until an operator switches it on. on_demand_watch (io.kestra.plugin.core.trigger.Webhook, keyed) accepts an on-demand sample of a named instance from a monitoring job or a laptop.
usage_workspace (io.kestra.plugin.core.flow.WorkingDirectory) opens one shared filesystem so the sampler, the aggregator, and the recommendation loop all read and write the same files.
capture_tempdb_usage (io.kestra.plugin.scripts.shell.Commands on the io.kestra.plugin.scripts.runner.docker.Docker runner, image mcr.microsoft.com/mssql-tools) writes a tempdb-usage.tsv fixture of six data files and two log rows plus a tempdb-growth.tsv fixture of growth events using heredocs, then runs a best effort sqlcmd against the instance with || true absorbing the exit code. A reachable instance overwrites the fixture with live rows, an unreachable one keeps the flow deterministic.
aggregate_usage (io.kestra.plugin.scripts.python.Script on python:3.12-slim) prefers the live sample, falls back to the fixture, and computes used_pct as the sum of used_mb over the sum of current_size_mb, growth_mb_per_hour from the fixture events, the per-file breakdown, the files above 80 percent, and the AT_RISK or HEALTHY verdict through the outputs protocol.
recommend_hot_files (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs right after the workspace closes and walks every hot file, with append_recommendation emitting one shrink or growth line per file through the outputs protocol.
check_threshold (io.kestra.plugin.core.flow.If) compares used_pct with usage_threshold_pct. Above it, alert_slack posts the numbers and open_tempdb_issue files a labeled GitHub issue carrying the markdown breakdown and the recommendations. At or below it, log_all_clear records the all-clear.
The errors block alerts Slack whenever the sample or the aggregation breaks, because a dead sampler looks exactly like a quiet instance.

What you get

Gateable metrics, verdict, used_pct, growth_mb_per_hour, and total_used_mb are flow outputs any downstream flow can branch on.
Early warning where engineers already work, a Slack ping with the used percentage and growth rate, plus a labeled GitHub issue with the per-file markdown table.
A recommendation file, one shrink or growth line per file sitting above 80 percent, ready to paste into a change ticket.
An audit trail, every execution is a dated record of how full tempdb was and how fast it was growing.

Who it's for

Database administrators who need warning before tempdb fills and takes the instance down.
SRE and platform teams running shared SQL Server estates who want capacity pressure routed into their existing queue.
Anyone who has been paged at 03:00 by a failed tempdb autogrow and wants that conversation moved earlier.

Why orchestrate this with Kestra

tempdb pressure is a slow moving problem that only hurts when nobody is watching. Kestra supplies the loop around it, a disabled-by-default hourly schedule as the safety net, a keyed webhook for on-demand samples, a containerized sqlcmd so the tooling version lives in the flow file, a threshold branch that separates escalation from the all-clear, a per-file recommendation loop, and execution history with the metrics attached. A cron entry on a jump box gives you none of that, it only samples the instance someone remembered to check.

Prerequisites

A worker with Docker access, the sampler runs in mcr.microsoft.com/mssql-tools and the aggregator in python:3.12-slim.
Network reachability to the SQL Server instance on port 1433 for a live sample. Without it the flow still completes on the bundled fixture, which is what makes it safe to run in CI or on a laptop.
A SQL login that can read tempdb.sys.database_files and call FILEPROPERTY.
A GitHub token that can open issues in your-org/database-ops (classic PAT scope repo, or fine-grained Issues: write).
A Slack incoming webhook for escalation and for failure alerts.

Secrets

TEMPDB_WEBHOOK_KEY: key segment guarding the on-demand webhook URL, anyone holding it can trigger a sample.
MSSQL_USER: SQL login name used by sqlcmd for the live tempdb sample.
MSSQL_PASSWORD: password for that SQL login.
GITHUB_TOKEN: token used to open the capacity issue.
SLACK_WEBHOOK_URL: Slack incoming webhook URL.

Quick start

Add the five secrets to your Kestra namespace.
Run the flow once with the default inputs and confirm verdict, used_pct, growth_mb_per_hour, and total_used_mb in the outputs. The bundled fixture lands above the default threshold, so the first run escalates on purpose.
Point the repository field of open_tempdb_issue at a repository where issues are welcome, then re-run and check the Slack message and the issue body.
Trigger on_demand_watch at /api/v1/{tenant}/executions/webhook/{namespace}/mssql-tempdb-growth-monitor/{TEMPDB_WEBHOOK_KEY}, or enable hourly_tempdb_watch once the worker can reach the instance.

How to extend

Watch a fleet by looping over an instance list, one execution per instance, and routing the results into a single digest.
Add a second gate on growth_mb_per_hour so a fast growing but still half empty tempdb escalates early.
Write the recommendations_md output to a file and ship it to S3 or attach it to the GitHub issue as a capacity artifact.
Turn a breach into a red run by adding io.kestra.plugin.core.execution.Fail under check_threshold.

Watch SQL Server tempdb Before It Fills the Instance

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Watch SQL Server tempdb Before It Fills the Instance

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Infrastructure Blueprints

Kong Ingress Upstream Target Unhealthy and Circuit Breaker Sentinel

Backport Kestra UI hotfixes to a Git review branch

Audit Keycloak Realm Security and Fix It with On-Call Approval

Gate a Kestra upgrade against every live flow

KMS Key Rotation Compliance Gate

Gate Kestra Subflow Changes Against Every Caller Before Deploying

New to Kestra?