Need help to troubleshoot a app job failure which connects to SQL Server

Sam 1,476 Reputation points
2023-07-19T17:40:01.6633333+00:00

Hi All,

All of a sudden from yesterday, one of the application job which connects to SQL Server is failing intermittently.
The job is scheduled every 15 mins. It sometimes gets executed successfully and sometimes fails. The user shares some generic SQLException and he feels that it is an issue with SQL Server.
The front app uses a web URL to connect to SQL server. The request first goes to the application load balance. From there, based on load, the request is then forwarded to any one of the 2 application servers.

The issue happened for last 10 hours and now the job is running fine. They use a SQL Authentication login to connect to SQL Server.
I saw the error logs for last 24 hours and I don't see any login failures as such. I wanted to collect a trace and see during the execution of job. Would that help identifying the issue?
The other suspicion is that, it could be an intermittent network issue. If it is a network issue, is there a simple dirty test to prove it is an intermittent network issue?
I tried to ping to the listener, not sure why it always shows request timed out but if I try to ping the nodes of AG, it works.

Environment:
SQL Server Version : Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64
Its an Microsoft Azure IaaS VM and 3 nodes always on setup. 2-sync replicas and 1-async DR replica.
Application or jobs connects using Listener,1433.

Error message:
org.activebpel.work.AeExceptionReportingWork.run(AeExceptionReportingWork.java:) at org.activebpel.work.AeWorkerThread.run(AeWorkerThread.java:156) Caused by: java.lang.reflect.InvocationTargetException at sun.reflect.GeneratedMethodAccessor1412.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:) at java.lang.reflect.Method.invoke(Method.java:) at .server.engine.storage.sql.AeConnectionInvocationHandlerFactory$AeRestoreAutoCommitHandler.invoke(AeConnectionInvocationHandlerFactory.java:) ... 11 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:) at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:) at

Needs some guidance on how to go about troubleshooting this issue.

Thanks,
Sam

SQL Server | Other
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-07-19T20:41:19.8566667+00:00

    That's a difficult case, but I would send this back to the developer. This sounds like the application is trying to run an operation on a connection that has been closed.

    Possibly, it could be that SQL Server has closed the connection because there was some accident. In that case, you should see stack dumps in the SQL Server errorlog.

    And, yes, intermittent network errors is also a possibility. Then again, how does errors manifest themselves in a Java client? This error sounds a bit like the API actually knows what is going on. That is, it knows that the connection is closed, because it has been closed, and now the called is still trying to use it.

    On the other hand, if a connection has been lost by some accident, the reaction of the API would be more confused.

    I would suggest to the developer to make a test. That is, program opens a connection, runs a query, sleeps for a minute or two with the connection open and try to run a new query. While the process is sleeping, pull the network cable.


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.