BookmarkSubscribeRSS Feed
cthnpe
SAS Employee

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.

 

 

6 REPLIES 6
cthnpe
SAS Employee

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
Super User

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
PROC Star

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
SAS Employee

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
SAS Employee

I shared the code as attached