Make sure SQL server agent job is not running before executing the same job TSQL
I would like to know the most efficient way to check if a SQL server agent job is running before executing the same job. Ideally if the job was running the logic would be such that it continues to check the job until completed and then executes the same job again. Please advise.
ASKER
So if the job was running the run_requested_date would be NULL? If that was the case how would I continuously check that job until the status had changed and then execute the job? Would there be some sort of loop necessary?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
job.job_id ,
activity.run_requested_dat
FROM msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id=activity.job_id
WHERE run_requested_date IS NOT NULL
AND stop_execution_date IS NULL
AND job.name LIKE 'Your Job Name%';