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
July 19, 2023 at 6:38 pm
Unfortunately that is a super generic java error from the sql driver, we've had a lot of issues with that and it's hard to point to a root cause. An actual login error would give you a specific error. You can try checking the logs for the application and see if that error is being thrown other places but that job happens to be one people notice.
It could be a network issue, it could be the sql server is just over loaded and rejecting the connection.
July 19, 2023 at 7:43 pm
If you don't see a logon failure event in the instance logs, that is 100% a developer problem to diagnose.
When connections do not happen in SQL Server, there's nothing in the error logs because there really isn't anything for SQL Server to report. However, that's not saying that there isn't a way to capture some of that information. In Extended Events, there's an event, connectivity_ring_buffer_recorded, that does show timeouts and stuff like that. I show an example here for SQL Monitor. Here's an example using DMVs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2023 at 9:58 am
Thank you everyone. It turned out to be a network issue.
August 10, 2023 at 5:20 am
thankyou for the important information .. i had same question
August 10, 2023 at 11:40 pm
Thank you everyone. It turned out to be a network issue.
What was the network issue???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply