Prevent SQL job running when another job is running

From SO post: https://stackoverflow.com/questions/200195/how-can-i-determine-the-status-of-a-job

CREATE VIEW myschema.Admin_RunningSQLJobs
AS
SELECT job.NAME
    ,job.job_id
    ,job.originating_server
    ,activity.run_requested_date
    ,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
JOIN (
    SELECT MAX(agent_start_date) AS max_agent_start_date
    FROM msdb.dbo.syssessions
    ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL
    AND stop_execution_date IS NULL

Using this view we can check for a running job before running our job…

In this example we don’t want the nightly pricing build to run if a monthly build is in progress, note, nightly is scheduled later than monthly. So wrap the call to the stored procedure in the job step like this…

IF NOT EXISTS(SELECT * FROM myschema.Admin_RunningSQLJobs WHERE name='Pricing - Monthly Rebuild') 
BEGIN 
 EXEC myschema.Nightly Pricing build
END

Note about backups:
We restore the live company into the test company of our ERP system, once a week, to keep the test company data fresh. It is worth noting that it is wise to keep the above SQL view in a database that is not involved in a restore, otherwise errors will occur trying to access the view as that database is in restore mode and cannot be accessed by the SQL job.

For this reason we keep this view in an “admin” database that is not part of the automated restore process, using the view in SQL jobs used against the test database prevents them erroring when the restore process in in progress.