DECLARE @job_name SYSNAME= N'Job_Uma', @latest_date DATE;
SELECT @latest_date = CONVERT(DATE, GETDATE());
IF EXISTS
(
SELECT 1
FROM msdb.dbo.sysjobactivity
LEFT JOIN msdb.dbo.sysjobhistory ON sysjobactivity.job_history_id = sysjobhistory.instance_id
JOIN msdb.dbo.sysjobs ON sysjobactivity.job_id = sysjobs.job_id
JOIN msdb.dbo.sysjobsteps ON sysjobactivity.job_id = sysjobsteps.job_id
AND ISNULL(sysjobactivity.last_executed_step_id, 0) + 1 = sysjobsteps.step_id
WHERE sysjobactivity.session_id =
(
SELECT TOP 1 session_id
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC
)
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
AND sysjobs.[name] = @job_name
)
PRINT 'The Job is currently running';
ELSE
IF EXISTS
(
SELECT 1
FROM msdb.dbo.sysjobs
LEFT JOIN
(
SELECT [job_id],
[run_date],
[run_status],
ROW_NUMBER() OVER(PARTITION BY [job_id] ORDER BY [run_date] DESC,
[run_time] DESC) AS RowNumber
FROM msdb.dbo.sysjobhistory
WHERE [step_id] = 0
) AS job_hist ON sysjobs.[job_id] = job_hist.[job_id]
AND job_hist.[RowNumber] = 1
LEFT JOIN msdb.dbo.sysjobactivity ON sysjobs.[job_id] = sysjobactivity.[job_id]
WHERE sysjobs.[name] = @job_name
AND CONVERT(DATE, CONVERT(CHAR(8), job_hist.[run_date])) >= @latest_date
AND job_hist.[run_status] = 1
)
PRINT 'The Job has already run successfully today';
ELSE
EXEC msdb.dbo.sp_start_job @job_name = @job_name;
