Troubleshoot a application job

Question

Troubleshoot a application job

vsamantha35

SSChampion

Points: 11551
More actions
July 19, 2023 at 5:38 pm
Go to Answer
#4239356

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
Grant Fritchey

SSC Guru

Points: 398904
More actions
July 20, 2023 at 1:24 pm
Answer
#4240111

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

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.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 2

If you don't see a logon failure event in the instance logs, that is 100% a developer problem to diagnose.

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

Thank you everyone. It turned out to be a network issue.

This reply was modified 1 year, 5 months ago by vsamantha35.

ismail3 Newbie Points: 1 More actions · Answer 4

thankyou for the important information .. i had same question

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 5

vsamantha35 wrote:

Thank you everyone. It turned out to be a network issue.

What was the network issue???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)