The other day Tom Roush (b/t) and Tim Radney (b/t) were having a discussion on twitter about using scheduled windows tasks to run SQL Processes. Now why would you ever want to do this? Well if you are running SQL Server Express then this may be your best or only option since SQL Agent isn’t included. However, one of the problems with this method is the lack of ability to tell if a job didn’t run, failed, ran late, ran long etc. I ended up chiming in with a suggestion to build a separate job that runs regularly (every hour, half hour, 2 hours, whatever is appropriate for your jobs) that checks if the tasks have run. Since we are not using the SQL Agent we have no system tables to work with. Well to be fair the system tables do exist, they just aren’t populated by the agent.
I’m going to do an example of tracking jobs that haven’t run yet even though they are scheduled to. First thing we need is to build our own schedule table. This would be fairly tricky if we were actually scheduling our jobs on this table but our task is somewhat simpler than that. So what we are going to do is create a table with the last scheduled run date, next scheduled run date, last run start and stop datetimes, and frequency. Each time a job runs it will update it’s LastRunStart and LastRunEnd values as the first and last step. Then when our monitor job runs it can check if the NextSchRunDate is both greater than the LastRunStart (hasn’t run yet) and less than the current date/time (should have run by now). Next it will update the Last & NextSchRunDate for those jobs where the NextSchRunDate is past. Unfortunately this is only going to handle simple schedules. Once a week, once a day, every other day, every four hours etc. It will not be able to easily handle a schedule like “Mondays and Thursdays”. For something like that you would need two entries and more complicated code in your job to update the correct row in the schedule table.
CREATE TABLE MySchedule (
JobId INT NOT NULL IDENTITY (1,1),
JobName VARCHAR(255),
IsActive BIT,
FrequencyType CHAR(2),
FrequencyAmt INT,
LastSchRunDate DATETIME,
NextSchRunDate DATETIME,
LastRunStart DATETIME,
LastRunEnd DATETIME
)
-- Sample data
INSERT INTO MySchedule VALUES
('Job runs every Monday', 1, 'wk', 1, '4/20/2015',
'4/27/2015', NULL, NULL)
INSERT INTO MySchedule VALUES
('Job runs the 3rd of each month', 1, 'mm', 1, '4/03/2015',
'5/03/2015', NULL, NULL)
INSERT INTO MySchedule VALUES
('Job runs every 4 hours', 1, 'hh', 4, '4/25/2015',
'4/25/2015 04:00:00', NULL, NULL)
CREATE PROCEDURE MonitorJobs AS
-- Report on late jobs
-- Maybe generate a table and use DBMail to send it out.
SELECT JobName, NextSchRunDate, LastRunStart
FROM MySchedule
WHERE IsActive = 1
AND NextSchRunDate < GETDATE()
AND (LastRunStart IS NULL
OR NextSchRunDate > LastRunStart
)
-- Update NextSchRunDate if the current report has been run.
--
-- Unfortunately you can't have a frequency type stored in a
-- column or variable so this has to be a bit of a hack. I
-- could either do this as a cursor & dynamic SQL updating
-- each row one at a time or a big case statement for each
-- frequency type.
--
-- The next difficulty is if both the last run and last schedule
-- run were more than one frequency type * frequency amount ago.
-- In order to get the "next" scheduled time we divide then multiply
-- by the FrequencyAmt to remove the remainder. This only works
-- because all of the values are integers.
--
UPDATE MySchedule
SET LastSchRunDate = NextSchRunDate,
NextSchRunDate =
CASE WHEN UPPER(FrequencyType) IN ('YEAR','YY','YYYY') THEN
DATEADD(YEAR, FrequencyAmt + DATEDIFF(YEAR, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('QUARTER','QQ','Q') THEN
DATEADD(QUARTER, FrequencyAmt + DATEDIFF(QUARTER, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('MONTH','MM','M') THEN
DATEADD(MONTH, FrequencyAmt + DATEDIFF(MONTH, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('DAYOFYEAR','DY','Y') THEN
DATEADD(DAYOFYEAR, FrequencyAmt + DATEDIFF(DAYOFYEAR, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('DAY','DD','D') THEN
DATEADD(DAY, FrequencyAmt + DATEDIFF(DAY, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('WEEK','WK','WW') THEN
DATEADD(WEEK, FrequencyAmt + DATEDIFF(WEEK, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('WEEKDAY','DW','W') THEN
DATEADD(WEEKDAY, FrequencyAmt + DATEDIFF(WEEKDAY, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('HOUR','HH') THEN
DATEADD(HOUR, FrequencyAmt + DATEDIFF(HOUR, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('MINUTE','MI','N') THEN
DATEADD(MINUTE, FrequencyAmt + DATEDIFF(MINUTE, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('SECOND','SS','S') THEN
DATEADD(SECOND, FrequencyAmt + DATEDIFF(SECOND, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
WHEN UPPER(FrequencyType) IN ('MILLISECOND','MS') THEN
DATEADD(MILLISECOND, FrequencyAmt + DATEDIFF(MILLISECOND, NextSchRunDate, GETDATE())
/ FrequencyAmt * FrequencyAmt,
NextSchRunDate)
END
WHERE IsActive = 1
AND NextSchRunDate < GETDATE()
AND NextSchRunDate < LastRunStartNow obviously this is fairly limited. It does however give a good starting place. For example if you need a history it wouldn’t be hard to modify the code to do inserts rather than updates. Or if you want long running jobs add an expected run time column and if the job has a start time but not an end time and the difference between the start time and now is longer than the expected time you send an alert.
Filed under: Microsoft SQL Server, Problem Resolution, SQL Agent Jobs, SQLServerPedia Syndication Tagged: language sql, microsoft sql server, SQL Agent Jobs, T-SQL 
