Sql-server – Connection problem different errors .net application

connection-poolingconnectivitysql server

We have a SQL Server with some relational databases. We have another webserver with applications which communicate with the databases.

We have an issue with one of our customers. We get some errors every night, and we have a problem identifying what the problem is. There is a web/wcf service.

We have traced the database with SQL Server Profiler and identified a lot of Connections with the following:

Audit login
RPC:Completed
Audit logout

Same SPID more than ten times. Is that normal behavior?

Errors

  1. 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.
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

  2. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

  3. 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=21033; handshake=0;
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    The wait operation timed out

  4. A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    The specified network name is no longer available

  5. Exception has been thrown by the target of an invocation.The wait operation timed out

  6. An error occurred on the server, but the error message and default error messages is missing, or could not be retrieved. System.UnauthorizedAccessException: UnauthorizedAccess

We need some advice how to do to identify the cause of the problem. We think it is several: network, queries performance etc.

Best Answer

I think you may be experiencing TCP Port Exhaustion on the Web Server. Avoiding TCP/IP Port Exhaustion is a good starting place for understanding the issue. This is generally an application design/implementation issue.

https://msdn.microsoft.com/en-us/library/aa560610%28v=bts.20%29.aspx?f=255&MSPPError=-2147217396

Related Question