AE fires sp_help_job without commit just to check other SQL job status

[PROSENJIT MAJI]

Hello Everyone,

We have noticed that whenever UC4 kicks off a SQL job ( lets call it as UC4.JOB.A ) & while it is in ACTIVE state, in parallel, it opens a transaction and continuously fires sp_help_job. 

Since it' a open tran without commit till the UC4.JOB.A ends, our other jobs which use tempDB are getting blocked. 

Question is - To check SQL job status ( like UC4.JOB.A ) why UC4 shoots sp_helpjob without a commit ? Why this is implemented so?

Example: Say below job is being monitored, UC4 fires below

begin tran

Exec sp_help_job@job_name='Sys_DB_IndexOptimize_User'

Since this is an open transaction with no commits till the job ends, it causes other blocking. Anyone have any suggestion on this please ?

------------------------------
Regards,
Prosenjit
------------------------------

[Pete Wirfs]

I find this interesting.  We don't use sp_help_job so we have not seen this concurrency issue, and I don't really know what the solution might be.  I wanted to learn more about it so I reviewed some examples I found online.  It seems it also builds a result set, and it seems it would need to hold an open unit of work to be able to provide such a result set.  Have you tried issuing your own commits?

We use a home-grown procedure that launches and monitors the execution of our SQLServer packages asynchronously.   Its not the best design, but it was invented a long time ago to support older versions of SQLServer.  Since it isn't broken, we're not changing it.   If I had it to do over again today, I would try to build a synchronous solution.

------------------------------
Pete
------------------------------