T-SQL: SQL Server Agent Job Execution Status
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
ConclusionIn 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.