CREATE VIEW myschema.Admin_RunningSQLJobs
,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
SELECT MAX(agent_start_date) AS max_agent_start_date
) 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')
EXEC myschema.Nightly Pricing build
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.