Execute job step if a job is not running

-> There are two SQL Server Agent jobs scheduled. Job1 has 6 steps and Job2 as 1 step.

-> Job1 details below,

Job Step Step Details
Step1 Changes Maxdop to 0.
Step2 Starts Job2.
Step3 Run reindexing for 3 Indexes.
Step4 Checks if Job2 is completed and waits for the Job2 to complete before proceeding to next step.
Step5 Changes Maxdop to 1.
Step6 Executes TSQL to create views.

-> Job2 details below,

Job Step Step Details
Step1 Run reindexing for 10 Indexes

-> Step4 of Job1 executes below TSQL that checks if Job2 has completed or not and wait for Job2 to complete.


DECLARE @JOB_NAME SYSNAME = N'JOBNAME';
DECLARE @JOB_NAME1 SYSNAME = N'JOBNAME'; 

Job_Status:
IF NOT EXISTS(
        select 1
        from msdb.dbo.sysjobs_view job
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
        where
            activity.run_Requested_date is not null
        and activity.stop_execution_date is null
        and job.name IN (@JOB_NAME,@JOB_NAME1)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
        )
BEGIN
    PRINT 'Job not running'
END
ELSE
BEGIN
    PRINT 'Job is running';
	waitfor delay '00:01:00'
	goto Job_Status;
END 

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s