Validating job execution

1

Hello community,
I have made a Job that will be firing one step every time every 10 seconds.
This unique step that it has will execute a stored procedure routine that analizes new data arrival and in my testing environment, it usually lasts around 6-8 seconds. Sometimes it could take 10-12 secs to finish...and when that happends, the thread gets stuck because a new thread its being executed again to run the procedure (that's what I am thinking).
Tried adding some logic before executing the stored procedure such as:

@isrunning=IIF(EXISTS(SELECT 1 FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
		WHERE sj.name = 'my-jobname' AND (sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL)),1,0);

but it doesn't work.
Will "sp_getapplock" and "sp_releaseapplock" features be a good call in order to avoid threads being queued?

Cheers,

2

I have never had to do anything like this but do have note about how it is done using Service Broker.

Service Broker Tickling OR How to Run a Procedure Asynchronously Every x Seconds (davewentzel.com)

You might want to investigate.

1 Like
3

Old stuff can get left in the sysactivity table. Be sure to add this check to other WHERE conditions:

AND sja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)

4

I've never done this either, so take this with a huge grain of salt. As an old embedded software developer, I would consider having a non-ending job that runs the required processing every 10 seconds provided the last processing pass was concluded, rather than launching a job every 10 seconds that might conflict with a previously-running instance. Just a thought.

1 Like
5

A job that runs for 8 secs every 10 secs to analyse new data seems a bit of madness to me. I would review the case study of why this would be necessary, and if you really need to do this , maybe look into the possibility of working with the transaction log or a second instance. You don't want to compromise speed and performance of live data for analysis purposes.

6

Hello @wakaguaka, I would keep the job running forever and would execute the sp every 10 seconds inside the job rather running the job every 10 sec.