CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

check if sql job is running

I have stored procedure that calls a sql job but the job is failing if 2 users make a call to the stored procedure at the same time so here is what I want to do:

  1. check if sql job is running first
  2. only execute the 2nd call if the first call finishes.

I have seen some examples where you can find out if job is running but can't seem to find out to put the 2nd call on hold and only execute when the first one completes.

DECLARE @job_name NVARCHAR(MAX) = 'mySQLJob'
EXEC msdb.dbo.sp_start_job @job_name = @mySQLJob

the error I am getting is something like "job is already running"

Upvotes: 2

Views: 6142

Answers (1)

TMonte
TMonte

Reputation: 108

sp_help_job can give you this information

EXEC msdb..sp_help_job @job_name = 'mySQLJob', @job_aspect = 'JOB'

current_execution_status Values - 1 Executing - 2 Waiting For Thread - 3 Between Retries - 4 Idle - 5 Suspended - 6 Obsolete - 7 PerformingCompletionActions

Upvotes: 4

Related Questions