Questa è una vecchia versione del documento!
Database scheduler
(scheduler per l'esecuzione pianificata di procedure SQL)
/* ============================================================================
TABLE-DRIVEN SCHEDULER FOR SQL SERVER
----------------------------------------------------------------------------
Creates:
- dbo.SchedulerConfig (job definitions: what to run, when, how)
- dbo.SchedulerConfigTimes (fixed daily run times, for ScheduleType = 'DAILY_TIME')
- dbo.SchedulerLog (execution history)
- dbo.sp_SchedulerDispatcher (main orchestrator, no CURSOR object used)
- supporting nonclustered indexes for the dispatcher's filter conditions
Intended usage: create a single SQL Server Agent Job with one step
EXEC dbo.sp_SchedulerDispatcher;
scheduled to run every minute. The actual per-job frequency is fully
controlled by the data in SchedulerConfig / SchedulerConfigTimes.
============================================================================ */
SET NOCOUNT ON;
GO
/* ----------------------------------------------------------------------------
1) CONFIGURATION TABLE
Stores the definition of every job. ScheduleType drives which of the other
scheduling columns/tables are actually used for that row.
---------------------------------------------------------------------------- */
IF OBJECT_ID('dbo.SchedulerConfig', 'U') IS NULL
BEGIN
CREATE TABLE dbo.SchedulerConfig
(
ID 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'''
-- 'INTERVAL' -> runs every FrequencyMinutes minutes
-- 'DAILY_TIME' -> runs at specific times of day, listed in SchedulerConfigTimes
-- 'ONE_TIME' -> runs once at SpecificDateTime, then never again
ScheduleType VARCHAR(15) NOT NULL,
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 CK_SchedulerConfig_ScheduleType
CHECK (ScheduleType IN ('INTERVAL','DAILY_TIME','ONE_TIME'))
);
END
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.
---------------------------------------------------------------------------- */
IF OBJECT_ID('dbo.SchedulerConfigTimes', 'U') IS NULL
BEGIN
CREATE TABLE dbo.SchedulerConfigTimes
(
ID INT IDENTITY(1,1) PRIMARY KEY,
ConfigID INT NOT NULL REFERENCES dbo.SchedulerConfig(ID),
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
);
END
GO
/* ----------------------------------------------------------------------------
3) EXECUTION LOG
Keeps an execution history for every run of every job, including duration
and any error raised.
---------------------------------------------------------------------------- */
IF OBJECT_ID('dbo.SchedulerLog', 'U') IS NULL
BEGIN
CREATE TABLE dbo.SchedulerLog
(
ID INT IDENTITY(1,1) PRIMARY KEY,
ConfigID INT NOT NULL, -- FK back to SchedulerConfig.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'
);
END
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 SchedulerConfig grows.
---------------------------------------------------------------------------- */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SchedulerConfig_Dispatch' AND object_id = OBJECT_ID('dbo.SchedulerConfig'))
BEGIN
CREATE NONCLUSTERED INDEX IX_SchedulerConfig_Dispatch
ON dbo.SchedulerConfig (ScheduleType, IsActive, IsRunning)
INCLUDE (ProcedureSchema, ProcedureName, Parameters, FrequencyMinutes, LastRunDate, StartTime, EndTime, WeekDays, SpecificDateTime);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_SchedulerConfigTimes_ConfigID' AND object_id = OBJECT_ID('dbo.SchedulerConfigTimes'))
BEGIN
CREATE NONCLUSTERED INDEX IX_SchedulerConfigTimes_ConfigID
ON dbo.SchedulerConfigTimes (ConfigID)
INCLUDE (RunTime, LastRunDate);
END
GO
/* ----------------------------------------------------------------------------
5) 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.
---------------------------------------------------------------------------- */
CREATE OR ALTER PROCEDURE dbo.sp_SchedulerDispatcher
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.SchedulerConfig
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.SchedulerConfig c
INNER JOIN dbo.SchedulerConfigTimes 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.SchedulerConfig
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 1 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.SchedulerConfig 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.SchedulerConfigTimes 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.SchedulerLog (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, @Count INT = (SELECT COUNT(*) FROM @DueJobs);
DECLARE @ConfigID INT, @Schema SYSNAME, @Name SYSNAME, @Params NVARCHAR(MAX), @LogID INT;
DECLARE @SQL 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 @SQL = QUOTENAME(@Schema) + '.' + QUOTENAME(@Name)
+ CASE WHEN @Params IS NOT NULL THEN ' ' + @Params ELSE '' END;
BEGIN TRY
EXEC (@SQL);
UPDATE dbo.SchedulerLog 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.SchedulerLog
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.SchedulerConfig SET IsRunning = 0 WHERE ID = @ConfigID;
SET @i += 1;
END
END
GO
/* ----------------------------------------------------------------------------
6) SAMPLE CONFIGURATION ROWS (optional — comment out or delete if not needed)
---------------------------------------------------------------------------- */
-- INSERT INTO dbo.SchedulerConfig (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.SchedulerConfig (JobName, ProcedureName, ScheduleType)
-- VALUES ('Daily Cost Recalc', 'sp_RecalcCosts', 'DAILY_TIME');
-- SET @NewConfigID = SCOPE_IDENTITY();
-- INSERT INTO dbo.SchedulerConfigTimes (ConfigID, RunTime)
-- VALUES (@NewConfigID, '08:00'), (@NewConfigID, '18:00');
-- INSERT INTO dbo.SchedulerConfig (JobName, ProcedureName, ScheduleType, SpecificDateTime)
-- VALUES ('Year-End Fix', 'sp_YearEndFix', 'ONE_TIME', '2026-12-31 23:55:00');
mauro.cortese