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