hobby:development:sql:database_scheduler

Differenze

Queste sono le differenze tra la revisione selezionata e la versione attuale della pagina.

Link a questa pagina di confronto

Prossima revisione		 Revisione precedente
hobby:development:sql:database_scheduler [2026/08/14 10:31] – creata mauro.cortesehobby:development:sql:database_scheduler [2026/08/14 14:00] (versione attuale) – mauro.cortese
Linea 5: Linea 5:
 \\ \\
  
 +Scheduler table-driven in SQL Server, basato su SQL Server Agent con una tabella di configurazione e una stored procedure "dispatcher".
  
 +=== Architettura ===
 +  - **Tabella di configurazione:** definisce quali procedure eseguire, con che frequenza, se sono attive
 +  - **Tabella di log:** traccia le esecuzioni (successo/errore, durata)
 +  - **Stored procedure dispatcher:** legge la config, decide cosa è "dovuto" eseguire, lancia le procedure con EXEC dinamico e gestione errori
 +  - **Un solo SQL Server Agent Job:** che gira ogni minuto (o ogni X minuti) e chiama il dispatcher
 +Vantaggio: non serve creare un job Agent per ogni procedura, ne basta uno solo che orchestra tutto in base alla tabella.
 +
 +
 +=== Creazione delle tabelle ===
 +<sxh sql>
 +-- -----------------------------------------------------------------------------
 +-- 1) CONFIGURATION TABLE
 +--    Stores the definition of every job. ScheduleType drives which of the other
 +--    scheduling columns/tables are actually used for that row.
 +-- -----------------------------------------------------------------------------
 +CREATE TABLE dbo.scheduler_config(
 +    IdJob               INT IDENTITY(1,1) PRIMARY KEY,
 +    JobName             NVARCHAR(100)     NOT NULL,                -- Human-friendly name, not used by the engine
 +    ProcedureSchema     SYSNAME           NOT NULL DEFAULT 'dbo',  -- Schema of the target procedure
 +    ProcedureName       SYSNAME           NOT NULL,                -- Name of the stored procedure to execute
 +    Parameters          NVARCHAR(MAX)     NULL,                    -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC'''
 +    ScheduleType        VARCHAR(15)       NOT NULL,                -- INTERVAL   -> runs every FrequencyMinutes minutes
 +                                                                   -- DAILY_TIME -> runs at specific times of day, listed in scheduler_config_times
 +                                                                   -- ONE_TIME   -> runs once at SpecificDateTime, then never again
 +    FrequencyMinutes    INT               NULL,                    -- required only when ScheduleType = INTERVAL
 +    SpecificDateTime    DATETIME          NULL,                    -- required only when ScheduleType = ONE_TIME
 +    StartTime           TIME              NULL,                    -- optional time-of-day window, applies to INTERVAL only
 +    EndTime             TIME              NULL,                    --        
 +    WeekDays            VARCHAR(20)       NULL,                    -- optional allowed weekdays (1=Monday..7=Sunday), applies to INTERVAL and DAILY_TIME
 +    IsActive            BIT               NOT NULL DEFAULT 1,      -- enable/disable without deleting the row
 +    IsRunning           BIT               NOT NULL DEFAULT 0,      -- prevents overlapping executions of the same job
 +    LastRunDate         DATETIME          NULL,                    -- timestamp of the last time this job started
 +      
 +    CONSTRAINT sK_Schedu_cerConfig_ScheduleType
 +        CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME'))
 +    );
 +GO
 + 
 +-- -----------------------------------------------------------------------------------
 +-- 2) FIXED DAILY RUN TIMES
 +--    Holds one or more fixed times of day for jobs whose ScheduleType = 'DAILY_TIME'.
 +--    Example: a job can run at both 08:00 and 18:00 by inserting two rows here.
 +-- -----------------------------------------------------------------------------------
 +CREATE TABLE dbo.scheduler_config_times (
 +    Id             INT IDENTITY(1,1) PRIMARY KEY,
 +    ConfigID       INT               NOT NULL REFERENCES dbo.scheduler_config(IdJob),
 +    RunTime        TIME              NOT NULL,        -- e.g. '08:00:00'
 +    LastRunDate    DATE              NULL             -- last calendar date this specific slot fired; prevents re-firing within the same day
 +    );
 +GO
 + 
 +-- ----------------------------------------------------------------------------
 +-- 3) EXECUTION LOG
 +--    Keeps an execution history for every run of every job, including duration
 +--    and any error raised.
 +-- ----------------------------------------------------------------------------
 +CREATE TABLE dbo.scheduler_log (
 +    ID              INT IDENTITY(1,1) PRIMARY KEY,
 +    ConfigID        INT               NOT NULL,   -- FK back to scheduler_config.ID
 +    ProcedureName   SYSNAME           NOT NULL,   -- denormalized for quick reading without a join
 +    StartDate       DATETIME          NOT NULL,
 +    EndDate         DATETIME          NULL,
 +    Outcome         VARCHAR(20)       NULL,       -- 'RUNNING' / 'OK' / 'ERROR'
 +    ErrorMessage    NVARCHAR(MAX)     NULL        -- populated only when Outcome = 'ERROR'
 +    );
 +GO
 + 
 +-- -----------------------------------------------------------------------------
 +-- 4) SUPPORTING INDEXES
 +--    The dispatcher filters on ScheduleType / IsActive / IsRunning on every run
 +--    (every minute), so these columns need a covering index to avoid a table
 +--    scan as scheduler_config grows.
 +-- -----------------------------------------------------------------------------
 +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfig_Dispatch
 +ON dbo.scheduler_config (ScheduleType, IsActive, IsRunning)
 +INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime);
 +GO
 +  
 +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfigTimes_ConfigID
 +ON dbo.scheduler_config_times (ConfigID)
 +INCLUDE (RunTime, LastRunDate);
 +GO
 +
 +</sxh>
 +
 +=== Dispatcher procedure ===
 +
 +<sxh sql>
 +
 +-- ----------------------------------------------------------------------------
 +-- DISPATCHER PROCEDURE
 +-- Main orchestrator, meant to be called every minute by a single SQL Server
 +-- Agent Job. Handles INTERVAL, DAILY_TIME and ONE_TIME scheduling.
 +-- No CURSOR object is used: due jobs are collected in a table variable and
 +-- walked with a WHILE loop, which is required only because each dynamic
 +-- EXEC needs its own isolated TRY/CATCH so one failing job never blocks
 +-- the others. All status/log updates that don't need per-row isolation
 +-- are done as set-based statements instead.
 +-- --------------------------------------------------------------------------
 +-- SAMPLE CONFIGURATION ROWS
 +-- -------------------------------------------------------------------------- */
 +-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, FrequencyMinutes, StartTime, EndTime, WeekDays)
 +-- VALUES ('Import CSV X3', 'sp_ImportCsvX3', 'INTERVAL', 15, '06:00', '22:00', '1,2,3,4,5');
 + 
 +-- DECLARE @NewConfigID INT;
 +-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType)
 +-- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME');
 +-- SET @NewConfigID = SCOPE_IDENTITY();
 +-- INSERT INTO dbo.scheduler_config_times (ConfigID, RunTime)
 +-- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00');
 + 
 +-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, SpecificDateTime)
 +-- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00');
 +-- ----------------------------------------------------------------------------
 +CREATE OR ALTER PROCEDURE dbo.sp_scheduler_dispatcher
 +AS
 +BEGIN
 +    SET NOCOUNT ON;
 + 
 +    DECLARE @Now DATETIME            = GETDATE();
 +    DECLARE @Today DATE              = CAST(@Now AS DATE);
 +    DECLARE @NowTime TIME            = CAST(@Now AS TIME);
 +    DECLARE @TodayWeekDay VARCHAR(1) = CAST(DATEPART(WEEKDAY, @Now) AS VARCHAR(1));
 + 
 +    -- Working table with a row number, used to walk through the due jobs one
 +    -- at a time without allocating a CURSOR object. LogID is filled later via
 +    -- an OUTPUT clause, avoiding a lookup query inside the loop.
 +    DECLARE @DueJobs TABLE (
 +        RowNum           INT IDENTITY(1,1) PRIMARY KEY,
 +        ConfigID         INT,
 +        ProcedureSchema  SYSNAME,
 +        ProcedureName    SYSNAME,
 +        Parameters       NVARCHAR(MAX),
 +        TimeSlotID       INT NULL,
 +        LogID            INT NULL
 +    );
 + 
 +    -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate,
 +    --    and (if set) we're inside the allowed time window and weekday list.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
 +        FROM dbo.scheduler_config
 +        WHERE IsActive = 1
 +            AND IsRunning = 0
 +            AND ScheduleType = 'INTERVAL'
 +            AND (LastRunDate IS NULL OR DATEDIFF(MINUTE, LastRunDate, @Now) >= FrequencyMinutes)
 +            AND (StartTime IS NULL OR @NowTime >= StartTime)
 +            AND (EndTime IS NULL OR @NowTime <= EndTime)
 +            AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%');
 + 
 +    -- 2) DAILY_TIME jobs: due when current time has reached a configured
 +    --    RunTime slot and that slot hasn't already fired today.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID
 +        FROM dbo.scheduler_config c
 +        INNER JOIN dbo.scheduler_config_times t ON t.ConfigID = c.ID
 +        WHERE c.IsActive = 1
 +            AND c.IsRunning = 0
 +            AND c.ScheduleType = 'DAILY_TIME'
 +            AND (c.WeekDays IS NULL OR c.WeekDays LIKE '%' + @TodayWeekDay + '%')
 +            AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today)
 +            AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108);
 + 
 +    -- 3) ONE_TIME jobs: due once, when current datetime has reached
 +    --    SpecificDateTime and the job has never run before.
 +    INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
 +        SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
 +        FROM dbo.scheduler_config
 +        WHERE IsActive = 1
 +            AND IsRunning = 0
 +            AND ScheduleType = 'ONE_TIME'
 +            AND LastRunDate IS NULL
 +            AND @Now >= SpecificDateTime;
 + 
 +    -- Nothing due: exit early, no further statements needed.
 +    IF NOT EXISTS (SELECT * FROM @DueJobs)
 +        RETURN;
 + 
 +    -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE,
 +    -- instead of doing it row-by-row inside the loop.
 +    UPDATE c SET
 +         c.IsRunning = 1
 +        ,c.LastRunDate = GETDATE()
 +    FROM dbo.scheduler_config c
 +        INNER JOIN @DueJobs d ON d.ConfigID = c.ID;
 + 
 +    -- Same for DAILY_TIME slots: stamp all fired slots at once.
 +    UPDATE t SET
 +        t.LastRunDate = @Today
 +    FROM dbo.scheduler_config_times t
 +        INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID
 +    WHERE d.TimeSlotID IS NOT NULL;
 + 
 +    -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated
 +    -- IDs directly via OUTPUT so the loop below needs no lookup query.
 +    DECLARE @LogMap TABLE (
 +         ConfigID INT
 +        ,LogID INT
 +        );
 + 
 +    INSERT INTO dbo.scheduler_log (ConfigID, ProcedureName, StartDate, Outcome)
 +         OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID)
 +         SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' FROM @DueJobs;
 + 
 +    UPDATE d SET
 +        d.LogID = m.LogID
 +    FROM @DueJobs d
 +        INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID;
 + 
 +    -- Execute each due procedure individually: dynamic SQL with per-job error
 +    -- handling genuinely requires row-by-row processing, so this is a plain
 +    -- WHILE loop keyed on RowNum rather than a CURSOR.
 +    DECLARE @i INT = 1;
 +    DECLARE @Count INT = (SELECT COUNT(*) FROM @DueJobs);
 +    DECLARE @ConfigID INT;
 +    DECLARE @Schema SYSNAME;
 +    DECLARE @Name SYSNAME;
 +    DECLARE @Params NVARCHAR(MAX)
 +    DECLARE @LogID INT;
 +    DECLARE @SqlCmd NVARCHAR(MAX);
 + 
 +    WHILE @i <= @Count
 +    BEGIN
 +        SELECT
 +            @ConfigID = ConfigID,
 +            @Schema   = ProcedureSchema,
 +            @Name     = ProcedureName,
 +            @Params   = Parameters,
 +            @LogID    = LogID
 +        FROM @DueJobs
 +            WHERE RowNum = @i;
 + 
 +        -- QUOTENAME() protects schema/procedure names against injection and
 +        -- reserved-word issues; parameters, if present, are appended as-is.
 +        SET @SqlCmd = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name) + CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END;
 + 
 +        BEGIN TRY
 +            EXEC (@SqlCmd);
 +            UPDATE dbo.scheduler_log SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID;
 +        END TRY
 +        BEGIN CATCH
 +            -- One failing procedure never stops the loop: error is logged,
 +            -- next job proceeds.
 +            UPDATE dbo.scheduler_log
 +            SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE()
 +            WHERE ID = @LogID;
 +        END CATCH
 + 
 +        -- Always release the running flag, whether the procedure succeeded or failed.
 +        UPDATE dbo.scheduler_config SET IsRunning = 0 WHERE ID = @ConfigID;
 + 
 +        SET @i += 1;
 +    END
 +END
 +GO
 +</sxh>
  • hobby/development/sql/database_scheduler.1786696274.txt.gz
  • Ultima modifica: 2026/08/14 10:31
  • da mauro.cortese