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.