DB connectivity Issue

Question

Hi Team,

We are facing frequent DB connectivity issues in one of PROD environment.
Environment:

Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Two Node AlwaysOn (Primary+Secondary) both are in same datacenter and no DR.

But we didn't find any error from SQL Server error logs.
But we got below info from application team:

From application side, the code is just trying to a simple DB connect and it’s getting various errors on

The following internal error has occurred:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Error Number: 5

Serial=9790 MacAddress=0 IP=10.x.x.x Process Info: Server Name=PNV033ABC Invoking LocationID=15256
QueryStr: dbo.pd_abc_TD_CustomersOutput

The following internal error has occurred:

Error Description: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.)
Error Number: 1225

The following internal error has occurred:

Error Description: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1818; handshake=30002;
Error Number: -2

Serial=8803 MacAddress=0 IP=10.x.x.x Process Info: Server Name=PNV0442318 Invoking LocationID=15425
QueryStr: dbo.pa_CPE_TD_CustomersOutput

One thing to note is even trying to connect to the DB through MSSQL still timeout

So it’s all pointing to connectivity issue to the database.

Answer 1

Hi @Vijay Kumar ,

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Error Number: 5

The timeout expired. The timeout expired prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

When you open an SQL Connection object, it always takes from an available pool of connections. When you close the connection, ASP.NETwill release the connection to the pool of connections so that next connection object can use it.If you open connections with out closing them and when the pool reaches maximum connections, it will throw the specified error. Make sure you are not opening connection inside loop, if you open connection make sure you are closing it immedietly after you execute the query.

The reason for the error is that the connections in the connection pool are used up. When a subsequent request establishes a database connection, there are no available resources (connections) in the connection pool, so they are allocated to the queue waiting for the connection pool allocation, and the waiting time exceeds the parameter“Connection Timeout”This exception is thrown.

increase the Connection and Command Timeout values to compensate and add code to your exception handlers to deal with this contingency.
https://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

If the device is in a workgroup, the device name isn't recognized by SQL Server.
If the device is in a domain, TCP/IP connections aren't enabled (Movere doesn't connect over named pipes).-us
For domain devices, enable TCP/IP connections. For workgroups, follow this article.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

there is a related issue you can refer to:https://stackoverflow.com/questions/26340644/sql-server-pre-login-handshake

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

That is a generic error and does not in any way help diagnose the actual issue.

If the problem is intermittent, it is almost 99% certainty to be a network connectivity issue or DNS name resolution issue. In rare circumstances, this can occur when the SQL Server is too busy to respond. Check your CPU and Memory usage.

Those errors come from the client, not SQL Server, so you will not find any error on the SQL Server side.

I would suggest you start here:
https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/

Answer 3

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Error Number: 5

This particular error makes me suspect that the application is not handling its connection objects properly, but are leaving them open and eventually fills up the connection pool. You should be able to see this condition on the SQL Server side by the fact that there are heaps of idle connection.

A correct handling of a connection object in a .NET application is to do:

using (SqlConnection cn = new SqlConnection(ConnSrtring) [
   ...
}

This makes sure that the connection object is disposed and that the connection goes back to the pool. If you only rely on garbage collection, it may take too long time until it actually happens.

The other two errors, on the other hand, suggests, as Tom says, that there are network issues.