Today morning I am trying to run the Job queue which was set as per below image.

Thank you for reading this post, don't forget to subscribe!

actually as per client it was failing almost every day, and raising the below error

A connection to SQL Server is no longer usable. This could be caused by one of the following reasons: * The server has been shut down manually or because of an error. * The SQL Server connection settings are not correct. * A network failure ...

 

Further investigate with the help of Microsoft,  we found that two others fields which we have in Job queue should always be mentioned:

1. “Maximum Number of Attempts to Run“
2. “Rerun Delay (sec.)“

To increase the robustness of these job queue entries, please set their “Maximum Number of Attempts to Run” to a feasible number (we use default value of 10 for standard Dataverse synch job queue entries), and give them a meaningful number in “Rerun Delay (sec.)” if you haven’t. This number is used like this: if you give n number of retries within one run of the job queue entry, “Rerun Delay (sec.)” determines the interval between those n attempts (this still counts as one job queue entry run).

This way, you give the chance to the job queue entry to try n times with a delay in between, before raising the red flag and setting itself in Error state. This makes the job queue entry robust to transient errors – especially useful if the job queue entry is calling a remote system, like in your case.

These crucial tools for the robustness of a job queue entry are described here:Read here

So next time if you face SQL Server error issue in SaaS version in Job queue please first check if you have entered those field’s value.

 

Thanks.