question

Miiimy7-1570 avatar image
0 Votes"
Miiimy7-1570 asked TomPhillips-1744 commented

SQL Agent job issu

Hello,

I got some problem here but i don't know what is it. My job is scheduled every 30 sec and it works but sometimes it's running late. Here is an example. The step is running late.

Can you help me please.
Thank you

94811-image.png


sql-server-generalsql-server-transact-sql
image.png (15.5 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The screenshot is much to small to see anything, it show also only some timestamps and the provided informations much to less; what e.g. is the job doing, T-SQL script or SSIS or ...?

0 Votes 0 ·

it's a powershell script.

0 Votes 0 ·

it's an issue that occurs not everytime. I have this feeling that it is due to " busy time " but i'm not so sure. The step job is connecting to a distant server and it's collecting information from this distant server to my actual server. This step job is running fast (3sec max) but the problem comes when it starts late. (10 sec late is ok, 15 is ok but more i'd rather not. What if it takes 1 hour to start running?)

0 Votes 0 ·

I've never heard of an Agent job starting one hour late (as long as Agent has been up and running).

But rather having Agent to start the job every thirty seconds, it may be better to code the Powershell script as infinite loop that sleeps for 30 seconds after having performed the task.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Miiimy-1570,

But is there a way to prioritize the job?

It seems we cannot. The @os_run_priority in the sp_add_jobstep is reserved and seem to have no effect.
Please refer to this thread which might help.
Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

SQL Agent jobs take time and resources to startup. 30 seconds is very short for an agent job. There is no guarantee it will run at exactly 30 seconds, or even 1 min.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes i think so as well. But is there a way to prioritize the job?

0 Votes 0 ·

No. If you need something to run every 30 seconds, you are better off creating a process which loops every 30 seconds, rather than a SQL Agent job.

Create a SQL Agent job which runs every 1 min and runs a process which technically never ends. If the job errors or ends unexpected, it will restart the process again.

0 Votes 0 ·