START / STOP JOB based on running status

In order to start a job or stop a job using sp_start_job or sp_stop_job will give error based on the current status of the job. So it is important to find the job status before executing the sp_start_job or sp_stop_job.

The following procedure can be used for both starting and stopping the job with the job name and condition as parameters. The procedure will execute the start or stop based on the status of the job (running or stopped).

create procedure sp_start_stop_job
(
@jobName sysname,
@startORstop varchar(30) — expects ‘START’ or ‘STOP’,

)
as

set nocount on

/*
This procedure will attempt to stop if it is running when ‘STOP’ parameter is specfied
and will start if it is stopped when ‘START’ parameter is specified.

*/

declare    @jobId UNIQUEIDENTIFIER,
@sysAdmimFlag INT,
@jobOwner   sysname,
@status int
declare    @results table (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)

select @jobId = job_id from msdb..sysjobs_view where name = @jobName
select @sysAdmimFlag = ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0)
select @jobOwner = SUSER_SNAME()

INSERT INTO @results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @sysAdmimFlag, @jobOwner, @jobId

–declare @status int
set @status = (select job_state from @results)

if @startORstop = ‘START’
begin
if @status <> 1 — job is not running
begin
exec msdb..sp_start_job @jobName
end
end
else — STOP
begin
if @status = 1 — job is running
begin
exec msdb..sp_stop_job @jobName
end
end

set nocount off

Advertisements

1 Comment »

  1. […] Start/Stop Job based on running status […]

RSS feed for comments on this post · TrackBack URI

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

%d bloggers like this: