Batch job run long time on VIYA3.5

[cthnpe]

We encountered an issue where a job inserting data into the SQL Server stops and does not continue. This issue occurs every 3-4 days. When we rerun the job, it can insert the data and continue working normally.

For example, running a JOB normally takes 20 minutes to insert data.  However, we found that running a JOB takes a long time and is not a complete job. No queries are running when we check the database (MS SQL Server).

Noted: We are using passthrough to query the data from the database.

[Kurt_Bremser]

What does the log say? What are the differences between successful and failed runs?

[cthnpe]

The attached log file shows that the file 20240528_064309_GROUP_09_02_AGP_PREP01 did not run successfully, as the last line does not contain an error message. On the other hand, the file 20240606_064001_GROUP_09_02_AGP_PREP01 ran to completion. From the log of the unsuccessful run, we checked with the database, and it was found that no processes occurred on that side.

[Kurt_Bremser]

In the successful run, the SELECT DISTINCT .... ran for less than half a minute, in the unsuccessful it took almost 30 minutes. I would start looking there.

[SASKiwi]

Is it always the SQL Server insert data step that is slow? If so then it would be worth tuning the SAS options DBCOMMIT and INSERTBUFF. If your job doesn't use these options then default values will be used and in my experience these are not optimal. I suggest you post your code so we can see what is happening. 

[cthnpe]

From the log of the unsuccessful run, we checked with the database and found that no processes occurred on that side. I attached my code in attachment.

[cthnpe]

I shared the code as attached