Building a job to monitor other jobs

5

May 4, 2015 by Kenneth Fisher

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 < LastRunStart

Now 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.

5 thoughts on “Building a job to monitor other jobs

  1. spaghettidba says:

    What about a replacement like Standalone SQL Agent? https://standalonesqlagent.codeplex.com/

  2. Wezi says:

    ^^^ Thats what she said 🙂 ^^^

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: