How to accurately detect if a SQL Server job is running and deal with the job already running?

Question

I'm currently using code like this to detect if a SQL server job is running. (this is SQL Server 2005, all SP's)

return (select isnull(  
(select top 1 CASE 
    WHEN current_execution_status = 4 THEN 0
    ELSE 1
    END
from openquery(devtestvm, 'EXEC msdb.dbo.sp_help_job')
where current_execution_status = 4 and
    name = 'WQCheckQueueJob' + cast(@Index as varchar(10))
), 1)
)

No problems there, and generally speaking, it works just fine.

But.... (always a but)

On occasion, I'll invoke this, get back a "job is not running" result, at which point I'll try and start the job, via

exec msdb.dbo.sp_start_job @JobName

and SQL will return that "SQLAgent has refused to start the job because it already has a pending request".

Ok. Also not a problem. It's conceivable that there's a slight window where the target job could get started before this code can start it, but after checking if it's started. However, I can just wrap that up in a try catch and just ignore the error, right?

begin try
if dbo.WQIsQueueJobActive(@index) = 0 begin
    exec msdb.dbo.sp_start_job @JobName
    break
end         
end try begin catch
    -- nothing here
end catch

here's the problem, though.

9 times out of 10, this works just fine. SQL agent will raise the error, it's caught, and processing just continues on, since the job is already running, no harm no foul.

But occasionally, I'll get a message in the Job History view (keep in mind the above code to detect if a specific job is running and start it if not is actually running from another job) saying that the job failed because "SQLAgent has refused to start the job because it already has a pending request".

Of course, this is the exact error that TRY CATCH is supposed to be handling!

When this happens, the executing job just dies, but not immediately from what I can tell, just pretty close. I've put logging all over the place and there's no consistency. One time it fails, it'll be at place a, the next time at place b. In some cases, Place A and place B have nothing but a

select @var = 'message'

in between them. Very strange. Basically, the job appears to be unceremoniously dumped and anything left to execute in the job is +not+ executed at all.

However, if I remove the "exec StartJob" (or have it invoked exactly one time, when I KNOW that the target job can't already be running), everything works perfectly and all my processing in the job runs through.

The purpose behind all this is to have a job started as a result of a trigger (among other things), and, if the job is already started, there's really no need to "start it again".

Anyone ever run into behavior like this with SQL Agent's Job handling?

EDIT: Current flow of control is like so:

1. Change to a table (update or insert)...
2. fires trigger which calls...
3. a stored proc which calls...
4. sp_Start_Job which...
5. starts a specific job which...
6. calls another stored proc (called CheckQueue) which...
7. performs some processing and...
8. checks several tables and depending on their contents might...
9. invoke sp_start_job on another job to start up a second, simultaneous job to process the additional work (this second job calls the CheckQueue sproc also but the two invocations operate on completely separate sets of data)

Answer 1

First of all, have you had a chance to look at service broker? From your description, it sounds like that's what you actually want.

The difference would be instead of starting a job, you put your data into a SB queue and SB will call your processing proc asynchronously and completely side-step issues with already-running jobs etc. It will auto spawn/terminate additional threads and demand dictates, it takes care of order etc.

Here's a good (and vaguely related) tutorial. http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

Let's assume that you can't use SB for whatever reason (but seriously, do!).

What about using the job spid's context_info.

1. Your job calls a wrapper proc that execs each step individually.

2. The first statement inside the wrapper proc is

   DECLARE @context_info VARBINARY(30)
   SET @context_info = CAST('MyJob1' AS VARBINARY)
   SET CONTEXT_INFO @context_info
   

3. When your proc finishes (or in your catch block)

   SET CONTEXT_INFO 0x0
   

4. When you are looking at calling your job, do this:

   IF NOT EXISTS (SELECT * FROM master..sysprocesses WITH (NOLOCK) WHERE context_info=CAST('MyJob1' AS VARBINARY))
       EXEC StartJob
   

When your wrapper proc terminates or the connection is closed, your context_info goes away.

You could also use a global temp table (i.e. ##JobStatus) They will disappear when all spids that reference it disconnect or if it's explicitly dropped.

Just a few thoughts.