Introduction

This article demonstrates a stored procedure which allows you to see if a SQL Server Agent Job is running. 

Problem

Sometimes you may have jobs that you want to ensure do not run at the same time. The aim of this article is to provide a solution to this issue.

Solution

Below is a small stored procedure that you can put at the beginning of a job that you can use to check for a potentially conflicting job.  If you notice a conflicting job is running, then you can take appropriate action such as using stop job. The following code shows this procedure: 

CREATE PROCEDURE [dbo].[usp_JobExecutionStatus]
@JobName sysname,
@Running bit OUTPUT
AS
  
DECLARE @Results TABLE
([Job ID] uniqueidentifier,
 [Last Run Date] varchar(20),
 [Last Run Time] varchar(20),
 [Next Run Date] varchar(20),
 [Next Run Time] varchar(20),
 [Next Run Schedule ID] varchar(50),
 [Requested To Run] varchar(10),
 [Request Source] varchar(10),
 [Request Source ID] varchar(50),
 [Running] varchar(5),
 [Current Step] varchar(5),
 [Current Retry Attempt] varchar(5),
 [State] varchar(5))
   
  
DECLARE @JobID uniqueidentifier
  
SELECT @JobID = job_id FROM msdb..sysjobs WHERE name = @JobName
  
INSERT INTO @Results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs = 1, @job_owner = '', @job_id = @JobID
  
SELECT @Running = Running FROM @Results
GO

Conclusion

In this article we showed how to avoid running multiple SQL Server Agent Jobs at the same time. We did it with a small and easy to use procedure.