Technical Article

Scheduler SP with Sample Log Creation/Exec Scripts

,

I created this SP for use with our morning DW builds. I use it to manage the execution of multiple jobs. It first starts up to a configureable number of jobs, monitors these jobs until one (or more) complete, and then starts additional jobs as needed. If any jobs fail, it will run the "transaction logs" (also posted) job, to clean up each of the databases, and then retry the failed jobs. A log file is also maintained during execution.

Parameters are:

@jobs = Comma seperated list of jobs to execute

@log = Integer that represents which log file to use

@retry_until_count = Number of times to retry a failed job

@retry_until_date = Only retry failed jobs until this date/time

@max_simultaneous = Maximum number of jobs to execute simultaneously

Please create/modify the LOG files as needed. Also you'll need the "transaction logs", "shrink dbs", and "sp_wait_job" posts.

Gabe Green

/*****************************************************
**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

Rate

Share

Share

Rate