June 27, 2019 at 6:55 am
Hi ,
We have two app servers Aap3 & Aap4.(Microsoft Dynamics app). The Database server is SQL Server 20014. Standard edition. The application accsessing the multiple dbs in db server. From couple of days users complaining that they facing frequent disconnectivity to SQL.
From Microsoft Dynamics the connection string time out setting is 30sec.
In the SQL Server -Properties--Connections--Allow remote connections to this server is 600.
Thier is no veeam backup or snapshot is goingon during the disconnectivity.
CPU/Memory utilization from our moniting software during this time frame also looks fine.
Also another observation from App03 and APP04 event viewer, this SQL disconnect from application servers are happening frerquenlty even if there is no issue reported by user., comparing the time frame of APP03 and APP04, time of these SQL disconnect event is not same that means if SQL serever in freeze, typcially both application servers should see the same event logs of SQL disconnection, that says there is something that application is causing the SQL DB reconnects when this issue occur.
Further reading dynamics configuration file, the timeout to SQL is set as 30s on application server, can we set this as 60s on application configuration ?
FromSQL DB configations and is their anything else I can recommend if there is any optimization we can do from SQL DB side ??
From Event viewer i found this below error.
The “error 26073 - TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket” Consider enabling the TCPAbortiveclose SQL Server registry setting and restarting the SQL Server.if problem p[lease contact technical support.
When i googled this error some says applu CU as per below link.
https://www.sqlserver-dba.com/2012/04/error-26073-and-tcp-connection-closed.html
Shall i suggest CU to customer? Or do i need to suggest increase the time out setting to 60 sec in Dynamics? or Any SQL Server i can perform any changes?
Please guide. What and all i can do to resolve the issue?
Thanks,
Jo
June 27, 2019 at 1:24 pm
Yes, get the CUs installed. That's very important.
Sure, you can increase the connection timeout, but it's not going to address the core issue.
The core issue is performance. You have long running and/or blocked queries which are leading to the timeouts. I'd focus there. It's dynamics, so your options are EXTREMELY limited, but it is still possible to add indexes and possibly plan guides to get enhanced performance. Also, look to the wait statistics to understand what is causing the server to slow down. That will help you make better decisions on how to fix things.
"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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply