SQL Task stuck in running state - VisualCron - Forum

Community forum

Etienne Lemire
2018-03-16T15:13:15Z
Hello,
We deployed VisualCron 8.3.2 on a test server and did setup multiple SQL Tasks starting Oracle packages. Everything was running fine.
Now we deployed VisualCron 8.3.4 on a production server and imported everything we had on the test server. Everything is still fine except for 2 nightly tasks that gets stuck in running state.

Those two tasks are usually taking around 2 hours to complete. When I connect in the morning, it is still running. I look in the database and VisualCron is not connected to it anymore. I then force the job to stop and we have the same problem the next night.

We have some other tasks doing almost the same thing that are running fine.

I just ran the job againg during the day today and start to monitor it. I can see that the job connects to the DB, and it does everything it has to do. Then, I see on the DB server that the VisualCron database connection ends but the task is still running in VisualCron.


I'm wondering if it could be a bug with version 8.3.4?
Support
2018-03-20T13:00:09Z
Can you try with a more complex procedure that just returns result of COUNT or similar?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-20T15:51:20Z
Hello Henrik,
I think you meant less complex. I tried a count and it works fine, after a couple of seconds, it triggers the next task.
Thanks!
Etienne Lemire
2018-03-22T18:09:10Z
Hello Henrik,
Would you like me to do more tests on that?
We have jobs that are not working and I will have to go back to an older version soon.
Support
2018-03-22T18:43:03Z
Yes, sorry, less complex. What if you create a stored procedure with a 2 hours sleep in it. To determine if it is related to the long time it runs?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-22T18:54:13Z
OK, we try it tonight and we'll see the result tomorrow morning.
Thanks!
Support
2018-03-22T19:36:09Z
By the way, could you tell us what kind of connection string you use in Oracle. You can replace host, user and pass? Also, which version of oracle are you using? Can you also tell us if you can refresh stored procedures correctly in the interface?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-23T13:19:37Z
I can reproduce the problem with the 2 hours sleep procedure. I scheduled it to run every 4 hours and it's stuck running since 4 PM yesterday (The firts time it ran).
I also tried to run it with a 2 minute sleep and it completed successfully.

The Oracle Server version is 11.2.0.3.
The Oracle client version was 11.2.0.3 on our VisualCron TEST server and 12.1.0.2 on our PROD server. To make sure the problem on the PROD server is not related to the Oracle client, I uninstalled it and installed version 11.2.0.3 and we still have the same problem.

I'm not sure what you mean here: "Can you also tell us if you can refresh stored procedures correctly in the interface?"

Here is the connection String:

Data Source=BICJTEST;User Id=HIDDEN;Password=**********;Unicode=true;

This is what we have in tnsnames.ora on the VisualCron server:
BICJTEST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HIDDEN)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=BICJTEST)
)
)
Support
2018-03-24T10:21:12Z
There is a Refresh button next to Stored procedures list. Does it properly work and list the Stored procedures?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-26T13:22:58Z
Hello Henrik,

On friday I did 2 tests :
I executed a procedure with a 55 minutes sleep and it succeed.
I executed a procedure with a 65 minutes sleep and it is still running.

I tried on two different servers and got the same result. It looks like something happens after 1 hours of execution.

About the Refresh button, I never tried it before because instead of using stored procedures, we use Oracle packages that we run using the SQL / Text tab.
As a test, I created a stored procedure and when I go to SQL / Stored procedure tab, the "Name of Stored Procedure" drop down is empty and the refresh button does nothing. I have to input the full procedure name and then I see I have to input the parameters.
Etienne Lemire
2018-03-26T14:51:39Z
I just tried the 65 minutes task on 3 Oracle instances and it succeed on one instance, it kept running on the other two instances.
Support
2018-03-26T18:01:12Z
Originally Posted by: Etienne Lemire 

I just tried the 65 minutes task on 3 Oracle instances and it succeed on one instance, it kept running on the other two instances.



What is the difference on the instances? Do you use different connection strings?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-26T20:05:23Z
The two not working are different instances on the same Windows server. Oracle version 11.2.0.3
The one working is on a Solaris server. Oracle version 11.2.0.4
The connection strings are written the same way.
Support
2018-03-26T20:33:18Z
Are you sure all are using Oracle->Native->Directmode?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-03-26T20:49:31Z
We are using Oracle->Native-> ?
The connection type in unchecked. If I check it, I see 5 choices in the list including "Standard 2 - direct mode (no Oracle Client)". Is it the one I should select?
Thanks!
Etienne Lemire
2018-03-27T13:38:09Z
I tried it with "Standard 2 - direct mode (no Oracle Client)" and it is still running after 65 minutes. I see the user isn't connected in the database anymore.
Here is the connection string
Provider=Oracle;data source=SERVERNAME;user=CJMCQ_JOBS;password=**********;direct=true;Port=1521;SID=BICJTEST;Service Name=BICJTEST;
Etienne Lemire
2018-03-29T12:27:01Z
Hello Henrik,

Yesterday, I installed version 8.3.4 on our TEST server and the jobs ran properly.
So the problem is not with version 8.3.4, it is not with the Oracle server, it must be something wrong with the Oracle client.
Support
2018-03-30T08:42:08Z
Thanks for the feedback!
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Etienne Lemire
2018-05-18T13:14:02Z
For the benefit of other users that could face the same issue.

We discovered that some kind of firewall is killing the connection after one hour because there is no activity (while the Oracle server process a very long query).
To avoid this, on the Oracle server, we added a parameter in the sqlnet.ora file:

SQLNET.EXPIRE_TIME=10

This parameter indicates to Oracle to look for death connections every 10 minutes. That also has the effect of keeping the network connection alive so the firewall won't kill the connection after one hour.

:o)
Support
2018-05-18T13:17:59Z
Originally Posted by: Etienne Lemire 

For the benefit of other users that could face the same issue.

We discovered that some kind of firewall is killing the connection after one hour because there is no activity (while the Oracle server process a very long query).
To avoid this, on the Oracle server, we added a parameter in the sqlnet.ora file:

SQLNET.EXPIRE_TIME=10

This parameter indicates to Oracle to look for death connections every 10 minutes. That also has the effect of keeping the network connection alive so the firewall won't kill the connection after one hour.

:o)



Thanks for the feedback!
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Ankeet
2021-03-26T22:23:14Z
I am facing the same issue today with MS SQL server. We upgraded our Visualcron server and clients today to 9.6.5.
My stored procedure simply brings back a few rows of data and runs in under 3 seconds when I run directly on the SQL server. Historically, that stored procedure has worked fine on VisualCron but today after the upgrade, it kept running in a loop. I deleted that step and recreated the DAtabase > SQL task with the same issue.

Any assistance is apprecaited.
Support
2021-03-30T08:14:17Z
Originally Posted by: Ankeet 

I am facing the same issue today with MS SQL server. We upgraded our Visualcron server and clients today to 9.6.5.
My stored procedure simply brings back a few rows of data and runs in under 3 seconds when I run directly on the SQL server. Historically, that stored procedure has worked fine on VisualCron but today after the upgrade, it kept running in a loop. I deleted that step and recreated the DAtabase > SQL task with the same issue.

Any assistance is apprecaited.



Hi Ankeet, please create an account with your official work mail. It makes it easier to identify you and help you.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Scroll to Top