Stored Procedures (2) to sequentially run SQL Server Agent Jobs.
use [master];
go
-- ============================================================================
-- Stored Procedure to Get SQL Job Execution Status
-- http://adventuresinsql.com/2010/01/stored-procedure-to-get-sql-job-execution-status/
-- ============================================================================
create procedure dbo.sp_dba_GetSqlJobExecutionStatus(
@job_name sysname,
@select_data int = 0,
@execution_status int = null output
)
as
begin
set nocount on;
/*-------------------------------------------------------
Is the execution status for the jobs.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions
-------------------------------------------------------*/
declare @job_id uniqueidentifier,
@is_sysadmin int,
@job_owner sysname;
select @job_id = jv.job_id,
@job_owner = sp.name
from msdb.dbo.sysjobs_view as jv
inner join sys.server_principals as sp
on jv.owner_sid = sp.sid
where jv.name = @job_name;
select @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);
create table #xp_results(
job_id uniqueidentifier not null,
last_run_date int not null,
last_run_time int not null,
next_run_date int not null,
next_run_time int not null,
next_run_schedule_id int not null,
requested_to_run int not null, -- BOOL
request_source int not null,
request_source_id sysname collate database_default null,
running int not null, -- BOOL
current_step int not null,
current_retry_attempt int not null,
job_state int not null
);
insert into #xp_results
execute master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin,
@job_owner,
@job_id;
select @execution_status = job_state from #xp_results;
drop table #xp_results;
if @select_data = 1
begin
select @job_name as job_name, @execution_status as execution_status
end;
set nocount off;
end;
go
exec sp_MS_marksystemobject 'sp_dba_GetSqlJobExecutionStatus';
-- ============================================================================
-- Stored Procedure to Sequentially Run SQL Agent Jobs
-- http://adventuresinsql.com/2010/01/stored-procedure-to-sequentially-run-sql-agent-jobs/#comment-208821
-- ============================================================================
create procedure dbo.sp_dba_run_unscheduled_jobs
@job_name_prefix sysname,
@max_minutes_runtime int = null,
@days_in_job_cycle int = 1,
@mail_job_name varchar(256) = '',
@mail_profile_name sysname = null,
@mail_recipients varchar(max) = null
as
begin
declare @job_name sysname,
@execution_status int,
@row_count int,
@last_run_date int,
@start_time datetime,
@stop_time datetime,
@min_date datetime,
@min_int_date int,
@int_date int;
select @min_date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())),
@days_in_job_cycle = case when @days_in_job_cycle < 1 then 1 else @days_in_job_cycle end; -- Prevent Infinite Loop
select
@min_int_date = YEAR(@min_date) * 10000 + MONTH(@min_date) * 100 + DAY(@min_date) - @days_in_job_cycle,
@int_date = YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE());
select @row_count = 1,
@job_name = '',
@start_time = GETDATE(),
@stop_time = DATEADD(mi, @max_minutes_runtime, GETDATE());
while @row_count > 0 and (@stop_time is null or @stop_time > GETDATE())
begin
select top 1 @job_name = sj.name,
@last_run_date = sjh.run_date
from msdb.dbo.sysjobs as sj
left outer join msdb.dbo.sysjobschedules as sjs
on sj.job_id = sjs.job_id
left outer join msdb.dbo.sysschedules as ss
on sjs.schedule_id = ss.schedule_id
and ss.enabled = 1
left outer join (select job_id,
MAX(run_date) as run_date
from msdb.dbo.sysjobhistory
where step_id = 0 and run_date > @min_int_date
group by job_id) as sjh
on sj.job_id = sjh.job_id
where(sjs.job_id is null or ss.schedule_id is null)
and sj.name like @job_name_prefix
and (sjh.run_date is null
or sj.name > @job_name
or sjh.run_date < @int_date)
and sj.enabled = 1
order by
ISNULL(sjh.run_date, 0),
sj.name;
select @row_count = @@ROWCOUNT;
if @row_count > 0
begin
exec dbo.sp_dba_GetSqlJobExecutionStatus
@job_name = @job_name,
@execution_status = @execution_status output;
if @execution_status = 4
begin --Make sure job is not running
exec msdb.dbo.sp_start_job @job_name = @job_name
end;
waitfor delay '00:00:02.000'; -- Pause here to make sure the job gets started before checking the status
exec dbo.sp_dba_GetSqlJobExecutionStatus
@job_name = @job_name,
@execution_status = @execution_status output;
while @execution_status != 4
begin
waitfor delay '00:00:01.000';
exec dbo.sp_dba_GetSqlJobExecutionStatus
@job_name = @job_name,
@execution_status = @execution_status output;
end;
end;
if @stop_time is not null
and @stop_time > GETDATE()
begin
declare @subject nvarchar(255), @body nvarchar(max);
select
@subject = @mail_job_name+' on '+@@SERVERNAME+' - Shutting down...Time limit reached.',
@body = @mail_job_name+' on '+@@SERVERNAME+' shut down after '+CAST(ABS(DATEDIFF(mi, GETDATE(), @start_time)) as nvarchar)+' minutes.';
exec msdb.dbo.sp_send_dbmail
@profile_name = @mail_profile_name,
@recipients = @mail_recipients,
@subject = @subject,
@body = @body;
end;
end;
end;
go
exec sp_MS_marksystemobject 'sp_dba_run_unscheduled_jobs';
go