question

ssurve avatar image
ssurve asked

Check if SQL job is running or not

If I execute below statement to check SQL job running status, it gives me correct result. DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' **But if I add order by START_EXECUTION_DATE DESC clause it fails.** DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID ORDER BY START_EXECUTION_DATE DESC; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' why?. Logically it should work.
tsqlsql serverdbasql-agent
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is the error? What version of SQL Server are you running this against?
0 Likes 0 ·
No sense re-inventing this wheel. @Pavel Pawlowski has published a working set of functions to do just what you are after - http://www.pawlowski.cz/2011/04/querying-agent-job-status-executing-waiting-job-completion-t-sql/
0 Likes 0 ·

1 Answer

·
JohnM avatar image
JohnM answered
My initial guess, without knowing what version of SQL Server or the exact error (if there is one) is that when setting the variable value in conjunction with an ORDER BY, you need to include a TOP 1. Like this: DECLARE @JOB_NAME NVARCHAR(100), @JOB_ID AS UNIQUEIDENTIFIER, @StopDate DATETIME; SET @JOB_NAME = N'MyJobName'; SELECT @JOB_ID = JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME=@JOB_NAME; SELECT TOP 1 @StopDate = STOP_EXECUTION_DATE FROM MSDB.DBO.SYSJOBACTIVITY WHERE JOB_ID=@JOB_ID ORDER BY START_EXECUTION_DATE DESC; IF @StopDate IS NOT NULL EXEC MSDB.dbo.SP_START_JOB @JOB_NAME; ELSE PRINT 'Error:Job is running' In testing, if I removed the TOP 1 it would just set the variable to a NULL value. Including the TOP 1 set the value correctly. I'm assuming that's the error, setting the variable to NULL. Hope that helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.