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

Entrambe le parti precedenti la revisione Revisione precedente
Prossima revisione		 Revisione precedente
hobby:development:sql:database_scheduler [2026/08/14 11:07] – mauro.cortesehobby:development:sql:database_scheduler [2026/08/14 14:00] (versione attuale) – mauro.cortese
Linea 4: Linea 4:
 \\ \\
 \\ \\
 +
 +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 === === Creazione delle tabelle ===
Linea 12: Linea 22:
 --    scheduling columns/tables are actually used for that row. --    scheduling columns/tables are actually used for that row.
 -- ----------------------------------------------------------------------------- -- -----------------------------------------------------------------------------
-CREATE TABLE dbo.SchedulerConfig(+CREATE TABLE dbo.scheduler_config(
     IdJob               INT IDENTITY(1,1) PRIMARY KEY,     IdJob               INT IDENTITY(1,1) PRIMARY KEY,
     JobName             NVARCHAR(100)     NOT NULL,                -- Human-friendly name, not used by the engine     JobName             NVARCHAR(100)     NOT NULL,                -- Human-friendly name, not used by the engine
Linea 19: Linea 29:
     Parameters          NVARCHAR(MAX)     NULL,                    -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC'''     Parameters          NVARCHAR(MAX)     NULL,                    -- Optional literal parameter string, e.g. '@Param1=1,@Param2=''ABC'''
     ScheduleType        VARCHAR(15)       NOT NULL,                -- INTERVAL   -> runs every FrequencyMinutes minutes     ScheduleType        VARCHAR(15)       NOT NULL,                -- INTERVAL   -> runs every FrequencyMinutes minutes
-                                                                   -- DAILY_TIME -> runs at specific times of day, listed in SchedulerConfigTimes+                                                                   -- DAILY_TIME -> runs at specific times of day, listed in scheduler_config_times
                                                                    -- ONE_TIME   -> runs once at SpecificDateTime, then never again                                                                    -- ONE_TIME   -> runs once at SpecificDateTime, then never again
     FrequencyMinutes    INT               NULL,                    -- required only when ScheduleType = INTERVAL     FrequencyMinutes    INT               NULL,                    -- required only when ScheduleType = INTERVAL
Linea 29: Linea 39:
     IsRunning           BIT               NOT NULL DEFAULT 0,      -- prevents overlapping executions of the same job     IsRunning           BIT               NOT NULL DEFAULT 0,      -- prevents overlapping executions of the same job
     LastRunDate         DATETIME          NULL,                    -- timestamp of the last time this job started     LastRunDate         DATETIME          NULL,                    -- timestamp of the last time this job started
-      +       
-    CONSTRAINT CK_SchedulerConfig_ScheduleType+    CONSTRAINT sK_Schedu_cerConfig_ScheduleType
         CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME'))         CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME'))
     );     );
 GO GO
 + 
 -- ----------------------------------------------------------------------------------- -- -----------------------------------------------------------------------------------
 -- 2) FIXED DAILY RUN TIMES -- 2) FIXED DAILY RUN TIMES
Linea 40: Linea 50:
 --    Example: a job can run at both 08:00 and 18:00 by inserting two rows here. --    Example: a job can run at both 08:00 and 18:00 by inserting two rows here.
 -- ----------------------------------------------------------------------------------- -- -----------------------------------------------------------------------------------
-CREATE TABLE dbo.SchedulerConfigTimes ( +CREATE TABLE dbo.scheduler_config_times ( 
-    ID             INT IDENTITY(1,1) PRIMARY KEY, +    Id             INT IDENTITY(1,1) PRIMARY KEY, 
-    ConfigID       INT               NOT NULL REFERENCES dbo.SchedulerConfig(ID),+    ConfigID       INT               NOT NULL REFERENCES dbo.scheduler_config(IdJob),
     RunTime        TIME              NOT NULL,        -- e.g. '08:00:00'     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     LastRunDate    DATE              NULL             -- last calendar date this specific slot fired; prevents re-firing within the same day
     );     );
 GO GO
 + 
 -- ---------------------------------------------------------------------------- -- ----------------------------------------------------------------------------
 -- 3) EXECUTION LOG -- 3) EXECUTION LOG
Linea 53: Linea 63:
 --    and any error raised. --    and any error raised.
 -- ---------------------------------------------------------------------------- -- ----------------------------------------------------------------------------
-CREATE TABLE dbo.SchedulerLog (+CREATE TABLE dbo.scheduler_log (
     ID              INT IDENTITY(1,1) PRIMARY KEY,     ID              INT IDENTITY(1,1) PRIMARY KEY,
-    ConfigID        INT               NOT NULL,   -- FK back to SchedulerConfig.ID+    ConfigID        INT               NOT NULL,   -- FK back to scheduler_config.ID
     ProcedureName   SYSNAME           NOT NULL,   -- denormalized for quick reading without a join     ProcedureName   SYSNAME           NOT NULL,   -- denormalized for quick reading without a join
     StartDate       DATETIME          NOT NULL,     StartDate       DATETIME          NOT NULL,
Linea 63: Linea 73:
     );     );
 GO GO
 + 
 -- ----------------------------------------------------------------------------- -- -----------------------------------------------------------------------------
 -- 4) SUPPORTING INDEXES -- 4) SUPPORTING INDEXES
 --    The dispatcher filters on ScheduleType / IsActive / IsRunning on every run --    The dispatcher filters on ScheduleType / IsActive / IsRunning on every run
 --    (every minute), so these columns need a covering index to avoid a table --    (every minute), so these columns need a covering index to avoid a table
---    scan as SchedulerConfig grows.+--    scan as scheduler_config grows.
 -- ----------------------------------------------------------------------------- -- -----------------------------------------------------------------------------
-CREATE NONCLUSTERED INDEX IX_SchedulerConfig_Dispatch +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfig_Dispatch 
-ON dbo.SchedulerConfig (ScheduleType, IsActive, IsRunning)+ON dbo.scheduler_config (ScheduleType, IsActive, IsRunning)
 INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime); INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime);
 GO GO
-  +   
-CREATE NONCLUSTERED INDEX IX_SchedulerConfigTimes_ConfigID +CREATE NONCLUSTERED INDEX sX_Schedu_cerConfigTimes_ConfigID 
-ON dbo.SchedulerConfigTimes (ConfigID)+ON dbo.scheduler_config_times (ConfigID)
 INCLUDE (RunTime, LastRunDate); INCLUDE (RunTime, LastRunDate);
-GO</sxh>+GO 
 + 
 +</sxh>
  
 === Dispatcher procedure === === Dispatcher procedure ===
  
 <sxh sql> <sxh sql>
 +
 -- ---------------------------------------------------------------------------- -- ----------------------------------------------------------------------------
 -- DISPATCHER PROCEDURE -- DISPATCHER PROCEDURE
Linea 93: Linea 106:
 -- are done as set-based statements instead. -- are done as set-based statements instead.
 -- -------------------------------------------------------------------------- -- --------------------------------------------------------------------------
-   SAMPLE CONFIGURATION ROWS+-- SAMPLE CONFIGURATION ROWS
 -- -------------------------------------------------------------------------- */ -- -------------------------------------------------------------------------- */
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType, FrequencyMinutes, StartTime, EndTime, WeekDays)+-- 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'); -- VALUES ('Import CSV X3', 'sp_ImportCsvX3', 'INTERVAL', 15, '06:00', '22:00', '1,2,3,4,5');
 + 
 -- DECLARE @NewConfigID INT; -- DECLARE @NewConfigID INT;
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType)+-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType)
 -- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME'); -- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME');
 -- SET @NewConfigID = SCOPE_IDENTITY(); -- SET @NewConfigID = SCOPE_IDENTITY();
--- INSERT INTO dbo.SchedulerConfigTimes (ConfigID, RunTime)+-- INSERT INTO dbo.scheduler_config_times (ConfigID, RunTime)
 -- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00'); -- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00');
- +  
--- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType, SpecificDateTime)+-- INSERT INTO dbo.scheduler_config (JobName, ProcedureName, ScheduleType, SpecificDateTime)
 -- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00'); -- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00');
 -- ---------------------------------------------------------------------------- -- ----------------------------------------------------------------------------
-CREATE OR ALTER PROCEDURE dbo.sp_SchedulerDispatcher+CREATE OR ALTER PROCEDURE dbo.sp_scheduler_dispatcher
 AS AS
 BEGIN BEGIN
     SET NOCOUNT ON;     SET NOCOUNT ON;
 + 
     DECLARE @Now DATETIME            = GETDATE();     DECLARE @Now DATETIME            = GETDATE();
     DECLARE @Today DATE              = CAST(@Now AS DATE);     DECLARE @Today DATE              = CAST(@Now AS DATE);
     DECLARE @NowTime TIME            = CAST(@Now AS TIME);     DECLARE @NowTime TIME            = CAST(@Now AS TIME);
     DECLARE @TodayWeekDay VARCHAR(1) = CAST(DATEPART(WEEKDAY, @Now) AS VARCHAR(1));     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     -- 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     -- at a time without allocating a CURSOR object. LogID is filled later via
Linea 130: Linea 143:
         LogID            INT NULL         LogID            INT NULL
     );     );
 + 
     -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate,     -- 1) INTERVAL jobs: due when enough minutes have passed since LastRunDate,
     --    and (if set) we're inside the allowed time window and weekday list.     --    and (if set) we're inside the allowed time window and weekday list.
     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
         SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL         SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
-        FROM dbo.SchedulerConfig+        FROM dbo.scheduler_config
         WHERE IsActive = 1         WHERE IsActive = 1
             AND IsRunning = 0             AND IsRunning = 0
Linea 143: Linea 156:
             AND (EndTime IS NULL OR @NowTime <= EndTime)             AND (EndTime IS NULL OR @NowTime <= EndTime)
             AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%');             AND (WeekDays IS NULL OR WeekDays LIKE '%' + @TodayWeekDay + '%');
 + 
     -- 2) DAILY_TIME jobs: due when current time has reached a configured     -- 2) DAILY_TIME jobs: due when current time has reached a configured
     --    RunTime slot and that slot hasn't already fired today.     --    RunTime slot and that slot hasn't already fired today.
     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
         SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID         SELECT c.ID, c.ProcedureSchema, c.ProcedureName, c.Parameters, t.ID
-        FROM dbo.SchedulerConfig c +        FROM dbo.scheduler_config c 
-        INNER JOIN dbo.SchedulerConfigTimes t ON t.ConfigID = c.ID+        INNER JOIN dbo.scheduler_config_times t ON t.ConfigID = c.ID
         WHERE c.IsActive = 1         WHERE c.IsActive = 1
             AND c.IsRunning = 0             AND c.IsRunning = 0
Linea 156: Linea 169:
             AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today)             AND (t.LastRunDate IS NULL OR t.LastRunDate < @Today)
             AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108);             AND CONVERT(CHAR(5), @NowTime, 108) >= CONVERT(CHAR(5), t.RunTime, 108);
 + 
     -- 3) ONE_TIME jobs: due once, when current datetime has reached     -- 3) ONE_TIME jobs: due once, when current datetime has reached
     --    SpecificDateTime and the job has never run before.     --    SpecificDateTime and the job has never run before.
     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)     INSERT INTO @DueJobs (ConfigID, ProcedureSchema, ProcedureName, Parameters, TimeSlotID)
         SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL         SELECT ID, ProcedureSchema, ProcedureName, Parameters, NULL
-        FROM dbo.SchedulerConfig+        FROM dbo.scheduler_config
         WHERE IsActive = 1         WHERE IsActive = 1
             AND IsRunning = 0             AND IsRunning = 0
Linea 167: Linea 180:
             AND LastRunDate IS NULL             AND LastRunDate IS NULL
             AND @Now >= SpecificDateTime;             AND @Now >= SpecificDateTime;
 + 
     -- Nothing due: exit early, no further statements needed.     -- Nothing due: exit early, no further statements needed.
     IF NOT EXISTS (SELECT * FROM @DueJobs)     IF NOT EXISTS (SELECT * FROM @DueJobs)
         RETURN;         RETURN;
 + 
     -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE,     -- Mark every due job as running and stamp LastRunDate in one set-based UPDATE,
     -- instead of doing it row-by-row inside the loop.     -- instead of doing it row-by-row inside the loop.
Linea 177: Linea 190:
          c.IsRunning = 1          c.IsRunning = 1
         ,c.LastRunDate = GETDATE()         ,c.LastRunDate = GETDATE()
-    FROM dbo.SchedulerConfig c+    FROM dbo.scheduler_config c
         INNER JOIN @DueJobs d ON d.ConfigID = c.ID;         INNER JOIN @DueJobs d ON d.ConfigID = c.ID;
 + 
     -- Same for DAILY_TIME slots: stamp all fired slots at once.     -- Same for DAILY_TIME slots: stamp all fired slots at once.
     UPDATE t SET     UPDATE t SET
         t.LastRunDate = @Today         t.LastRunDate = @Today
-    FROM dbo.SchedulerConfigTimes t+    FROM dbo.scheduler_config_times t
         INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID         INNER JOIN @DueJobs d ON d.TimeSlotID = t.ID
     WHERE d.TimeSlotID IS NOT NULL;     WHERE d.TimeSlotID IS NOT NULL;
 + 
     -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated     -- Bulk-insert one 'RUNNING' log row per due job, capturing the generated
     -- IDs directly via OUTPUT so the loop below needs no lookup query.     -- IDs directly via OUTPUT so the loop below needs no lookup query.
Linea 193: Linea 206:
         ,LogID INT         ,LogID INT
         );         );
- +  
-    INSERT INTO dbo.SchedulerLog (ConfigID, ProcedureName, StartDate, Outcome)+    INSERT INTO dbo.scheduler_log (ConfigID, ProcedureName, StartDate, Outcome)
          OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID)          OUTPUT inserted.ConfigID, inserted.ID INTO @LogMap (ConfigID, LogID)
          SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' FROM @DueJobs;          SELECT ConfigID, ProcedureName, GETDATE(), 'RUNNING' FROM @DueJobs;
 + 
     UPDATE d SET     UPDATE d SET
         d.LogID = m.LogID         d.LogID = m.LogID
     FROM @DueJobs d     FROM @DueJobs d
         INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID;         INNER JOIN @LogMap m ON m.ConfigID = d.ConfigID;
 + 
     -- Execute each due procedure individually: dynamic SQL with per-job error     -- Execute each due procedure individually: dynamic SQL with per-job error
     -- handling genuinely requires row-by-row processing, so this is a plain     -- handling genuinely requires row-by-row processing, so this is a plain
Linea 214: Linea 227:
     DECLARE @LogID INT;     DECLARE @LogID INT;
     DECLARE @SqlCmd NVARCHAR(MAX);     DECLARE @SqlCmd NVARCHAR(MAX);
 + 
     WHILE @i <= @Count     WHILE @i <= @Count
     BEGIN     BEGIN
Linea 225: Linea 238:
         FROM @DueJobs         FROM @DueJobs
             WHERE RowNum = @i;             WHERE RowNum = @i;
 + 
         -- QUOTENAME() protects schema/procedure names against injection and         -- QUOTENAME() protects schema/procedure names against injection and
         -- reserved-word issues; parameters, if present, are appended as-is.         -- 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;         SET @SqlCmd = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name) + CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END;
 + 
         BEGIN TRY         BEGIN TRY
             EXEC (@SqlCmd);             EXEC (@SqlCmd);
-            UPDATE dbo.SchedulerLog SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID;+            UPDATE dbo.scheduler_log SET EndDate = GETDATE(), Outcome = 'OK' WHERE ID = @LogID;
         END TRY         END TRY
         BEGIN CATCH         BEGIN CATCH
             -- One failing procedure never stops the loop: error is logged,             -- One failing procedure never stops the loop: error is logged,
             -- next job proceeds.             -- next job proceeds.
-            UPDATE dbo.SchedulerLog+            UPDATE dbo.scheduler_log
             SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE()             SET EndDate = GETDATE(), Outcome = 'ERROR', ErrorMessage = ERROR_MESSAGE()
             WHERE ID = @LogID;             WHERE ID = @LogID;
         END CATCH         END CATCH
 + 
         -- Always release the running flag, whether the procedure succeeded or failed.         -- Always release the running flag, whether the procedure succeeded or failed.
-        UPDATE dbo.SchedulerConfig SET IsRunning = 0 WHERE ID = @ConfigID; +        UPDATE dbo.scheduler_config SET IsRunning = 0 WHERE ID = @ConfigID; 
 + 
         SET @i += 1;         SET @i += 1;
     END     END
  • hobby/development/sql/database_scheduler.1786698453.txt.gz
  • Ultima modifica: 2026/08/14 11:07
  • da mauro.cortese