Is Job Running?

The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise an error and not proceed to the next step.

sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @ExecutionStatus INT
DECLARE @JobName VARCHAR(100) = '%yourjobnamehere%'

SELECT name, current_execution_status, job_id
INTO #Jobs
FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',
'set fmtonly off exec msdb.dbo.sp_help_job')

SELECT @ExecutionStatus = current_execution_status
FROM #Jobs
WHERE job_id = (SELECT sj.job_id
                FROM msdb.dbo.sysjobs sj
                WHERE name LIKE @JobName)

IF @ExecutionStatus != 1
BEGIN
    RAISERROR ('Job Is Not Running',
               16,
               1
               )
END

The only caveat to this method, which is the only one I know, is that you need to enable ‘Ad Hoc Distributed Queries’. While I don’t consider this a harmful change, it may not be allowed unless you have dbo permissions on the database.

Featured Articles

 Site Author

  • Thanks for visiting!