Scheduler SP with Sample Log Creation/Exec Scripts

/*****************************************************
**	Sample script that can be run from a job
*****************************************************/
declare	@jobs varchar(2000)

-- set up jobs to run here, seprate each with a comma

set @jobs = '' +
	'update inventory orders and cubes,' +
	'demand flow jobs,' +
	'focus order information,' +
	'serial numbers,' +
	'inventory balances,' +
	'get_sears_report,' +
	'model prices,' +
	'focus parts availability,' +
	'focus invoice,' +
	'dm pull,' +
	'daily orders,' +
	'corp item cost,' +
	'cust cops info xfer,' +
	'open orders detail,' +
	'production quantities,' +
	'ship dates,' +
	'unsaleable inventory,'

truncate table master_job_log

exec sp_master_job_scheduler @jobs,1, 5, null, 6

/*****************************************************
**	Create script for a sample log file
*****************************************************/
CREATE TABLE [dbo].[master_job_log] (
	[step] [int] NOT NULL ,
	[description] [varchar] (30) NULL ,
	[result] [varchar] (20) NULL ,
	[start] [datetime] NULL default(getdate()),
	[stop] [datetime] NULL default(getdate())
)

/*****************************************************
**	Job scheduler procedure script
*****************************************************/
CREATE PROCEDURE [sp_master_job_scheduler] 
	@jobs varchar(2000),
	@log tinyint = 1,
	@retry_until_count int = 25,
	@retry_until_date datetime = null,
	@max_simultaneous tinyint = 3
AS
/*
	set the job names up in this string...

	seperate each with a comma,
	first character determines serial or asynchronous
	job execution

	set @jobs = '' +
	'corp_item_cost,' +
	'serial numbers,'
*/
declare
	@log_nm varchar(50),
	@line varchar(500),
	@job varchar(50),
	@job30 varchar(30),
	@result_text varchar(30),
	@runtime int,
	@stop datetime,
	@step int,
	@b int,
	@e int,
	@result int,
	@today datetime,
	@max_step int,
	@running int,
	@failed int,
	@notstarted int,
	@action int,
	@start_of_loop bit,
	@max_runtime int
begin
	set nocount on

	-- static config values
	set @max_runtime = 10800	-- 3 hours

	-- create temp table
	create table #master_scheduler_jobs(
		[step] int identity(1,1) primary key,
		[job] varchar(50),
		[start] datetime null,
		[stop] datetime null,
		[result] int default(0),
		[retries] int default(0),
		[runtime] int default(0)
	)

	-- set appropriate log name
	set @log_nm = case @log
		when 2 then 'daily_job_log'
		when 3 then 'hourly_job_log'
		when 4 then 'triweekly_job_log'
		else 'master_job_log' end

	-- do initial build of job table
	set @jobs = rtrim(ltrim(@jobs))

	if right(@jobs,1)<>','
	begin
		set @jobs = @jobs + ','
	end

	set @b = 0
	set @e = charindex(',',@jobs,@b+1)
	
	while (@e>0)
	begin
		set @job = substring(@jobs,@b+1,@e-(@b+1))

		insert into #master_scheduler_jobs([job]) values(@job)

		select @step = max([step]) from #master_scheduler_jobs
		set @job30 = left(@job,30)

		set @line = 'insert into '+@log_nm+' ([step],[description],[result],[start],[stop]) values ('+convert(varchar,@step)+','''+@job30+''',''Not Started'',null,null)'
		exec(@line)

		set @b = @e
		set @e = CHARINDEX(',',@jobs,@b+1)
	end

	-- quick validity check for retry counters
	if @retry_until_count<0
	begin
		set @retry_until_count = 0
	end

	if (datediff(second,getdate(),@retry_until_date)<0) and (@retry_until_date is not null)
	begin
		set @retry_until_date = getdate()
	end

	-- start main logic
	set @step = 0
	set @start_of_loop = 1

	select 
		@running = sum(case when ([result]=-2 and [runtime]<@max_runtime) then 1 else 0 end),
		@failed = sum(case when ([result]=0 and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) then 1 else 0 end),
		@notstarted = sum(case when ([start] is null) then 1 else 0 end),
		@max_step = max([step])
	from 
		#master_scheduler_jobs 
	where 
		([result]=-2 and [runtime]<@max_runtime) or 
		(([result]=0 or [result]=-1) and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) or 
		([start] is null)

	while (@running>0) or (@failed>0) or (@notstarted>0)
	begin
		-- increment step pointer
		if @step>=@max_step
		begin
			set @step = 0
			set @start_of_loop = 1
		end

		select
			@step = min([step])
		from 
			#master_scheduler_jobs 
		where 
			(([result]=-2 and [runtime]<@max_runtime) or 
			(([result]=0 or [result]=-1) and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) or 
			([start] is null)) and
			([step]>@step)

		-- get the action
		select
			@action = case
				when ([result]=-2 and [runtime]<@max_runtime) then -2	-- already executing
				when (([result]=0 or [result]=-1) and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) then 0	-- failed
				else -1	-- not started
				end,
			@job = [job]
		from
			#master_scheduler_jobs
		where 
			(([result]=-2 and [runtime]<@max_runtime) or 
			(([result]=0 or [result]=-1) and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) or 
			([start] is null)) and
			([step]=@step)
			
		-- wait for up to 1 hour for the transaction log and shrink db to stop running
		set @b = @max_runtime / 2	-- wait half of the max wait, for this job
		exec @result = sp_wait_job 'transaction logs,shrink dbs', 0, @b, 30, 1	-- even count the RETRYING status as running

		-- determine actions for job

		if (@action=0 or @action=-1) and (@running<@max_simultaneous)

		begin
			-- mark the job logs
			update #master_scheduler_jobs
			set
				[start] = getdate(),
				[stop] = null,
				[retries] = case when @action=0 then [retries] + 1 else 0 end
			where
				[step] = @step

			set @today = getdate()
			set @job30 = left(@job,30)

			set @line = 'update '+@log_nm+' set [start]='''+convert(varchar,@today)+''',[stop]=null,[result]=''Started'' where [step]='+convert(varchar,@step)

			exec(@line)

			-- start the job
			exec msdb..sp_start_job @job_name=@job

			-- wait for the job to indicate started
			exec @result = sp_wait_job @job, 1, 30, 5, 1
		end
				
		-- get latest status on job
		exec @result = sp_wait_job @job, 0, 15, 5, 1

		-- mark the status in logs
		update #master_scheduler_jobs
		set
			[stop] = case when @result=0 or @result=1 or @result=3 then getdate() else null end,
			[runtime] = case when [start] is not null then datediff(second,[start],getdate()) else 0 end,
			[result] = @result
		where
			[step] = @step

		set @today = getdate()
		set @result_text = case @result
			when -2 then 'Executing'
			when -1 then 'Not Started'
			when 1 then 'Successfull'
			when 2 then 'Retrying'
			when 3 then 'Canceled'
			else 'Failed' end

		if @result in (0,1,3)
			set @line = 'update '+@log_nm+' set [stop]='''+convert(varchar,@today)+''',[result]='''+@result_text+''' where [step]='+convert(varchar,@step)
		else
			set @line = 'update '+@log_nm+' set [stop]=null,[result]='''+@result_text+''' where [step]='+convert(varchar,@step)

		exec(@line)
		
		-- get the latest log counts
		select 
			@running = sum(case when ([result]=-2 and [runtime]<7200) then 1 else 0 end),
			@failed = sum(case when ([result]=0 and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) then 1 else 0 end),
			@notstarted = sum(case when ([start] is null) then 1 else 0 end),
			@max_step = max([step])
		from 
			#master_scheduler_jobs 
		where 
			([result]=-2 and [runtime]<@max_runtime) or 
			(([result]=0 or [result]=-1) and [start] is not null and [retries]<=@retry_until_count and (@retry_until_date is null or datediff(second,getdate(),@retry_until_date)>=0)) or 
			([start] is null)

		-- start the transaction job, to clean up possible errors that may be causing job failure... if it hasn't started in the mean time
		if @failed>0 and @notstarted=0 and @start_of_loop=1
		begin
			exec @result = sp_wait_job 'transaction logs', 0, 4, 1, 1	-- even count the RETRYING status as running

			if @result in (0,1,3)		-- job is currently stopped (in "cancelled" or "successfull" or "failed" status)
			begin
				exec msdb..sp_start_job @job_name='Transaction Logs'	-- start the job
				waitfor delay '00:00:05'
			end
		end

		set @start_of_loop = 0
	end
			
	-- drop temp table
	drop table #master_scheduler_jobs

end