SQL script task

A SQL script task executes the same SQL script on multiple databases concurrently.

When to use a SQL script task

A SQL script task is especially useful in multi-tenant environments or managed service provider (MSP) setups where identical operations must be applied consistently across a large number of databases, with built-in concurrency control, automatic retries, and per-database logging.

Common use cases include:

  • Scheduled maintenance: rebuild indexes, reclaim unused space, run VACUUM / ANALYZE (PostgreSQL), ALTER INDEX ... REBUILD (SQL Server), or OPTIMIZE TABLE (MySQL/MariaDB) across your entire database fleet on a weekly or nightly schedule.
  • Schema deployment: apply DDL changes such as ALTER TABLE, CREATE INDEX, or stored procedure updates to all tenant databases at once, ensuring consistency across environments.
  • Data housekeeping: purge expired sessions, archive old records, clean up log tables, or rotate partitions on a regular schedule.
  • Compliance and audit checks: verify that security settings, user permissions, or constraints are consistently applied across every database.
  • Batch processing: run end-of-month calculations, refresh materialized views, or update aggregation tables across multiple databases in parallel.

NB: If your goal is to retrieve and collect query results (SELECT) rather than execute actions, consider using a SQL report task instead.

Log content

Database execution logs contain messages produced by the SQL script for a dedicated database. The way to emit log messages depends on the database engine.

PostgreSQL

Use RAISE to write messages to the log:

  • RAISE DEBUG, RAISE LOG, RAISE INFO, RAISE NOTICE and RAISE WARNING are sent to stdout
  • RAISE EXCEPTION is sent to stderr
DO $$
BEGIN
  RAISE NOTICE 'Starting maintenance...';
  -- your SQL statements here
  RAISE NOTICE 'Maintenance complete.';
END $$;

SQL Server (MSSQL)

Use PRINT or RAISERROR to produce log output:

  • PRINT and RAISERROR with a severity of 10 or below are sent to stdout
  • RAISERROR with a severity strictly greater than 10 are sent to stderr
PRINT 'Starting maintenance...';
-- your SQL statements here
RAISERROR('Maintenance complete.', 0, 1) WITH NOWAIT;

RAISERROR ... WITH NOWAIT flushes the message immediately, which is useful for long-running scripts.

Oracle

Use DBMS_OUTPUT.PUT_LINE to write messages to the log:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting maintenance...');
  -- your SQL statements here
  DBMS_OUTPUT.PUT_LINE('Maintenance complete.');
END;

Due to internal buffering, all log messages are flushed only at the end of the execution. As a result, they will all share the same timestamp corresponding to the end of processing.

MySQL / MariaDB

MySQL and MariaDB do not provide a built-in mechanism to emit informational messages during script execution. Only the final error message (if any) is captured in the log.

Task characteristics

In addition to the common task characteristics, here are those specific to SQL script tasks.

Database Set filter

Filter (by names and tags) the databases on which the SQL script will be run.

NB: This filter is combined with the filter applied at the project level.

Max. number of concurrent database executions

When a SQL script is executed on several databases, you can specify the maximum number of databases to be processed concurrently.

This lets you control the load on your infrastructure — for example, limiting parallel executions during peak hours.

Max. number of script execution retries per database

Maximum number of retry attempts if the SQL script execution fails on a database. Defaults to 0 (no retry).

Retries are useful for transient errors such as temporary network issues or database lock contention.

Delay between retries

Delay in seconds between retry attempts. Defaults to 0 (immediate retry).

Adding a delay can help avoid repeatedly hitting the same transient issue.

Log output

Controls when database execution logs are retained:

  • Always – logs are kept for every execution
  • On failure or abort – logs are kept only when the execution fails or is aborted
  • Never – logs are discarded