Connection problem different errors .net application

Question

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.

Answer 1

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

Answer 2

The "Connection Timeout expired" error commonly occurs in the following cases:

• An instance of the SQL Server Database Engine is not running.
• The SQL Server Browser service is not running.
• The TCP/IP is disabled.
• The server name was typed incorrectly.
• There are network problems.
• The TCP/IP port for the Database Engine instance is blocked by a firewall.
• The client and server are not configured to use the same network protocol.

To check how to trace this error based on the above reasons check Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

Answer 3

I recently experienced error 3 above - Connection Timeout Expired.

SQL Server was running on a server that had been virtualized. Somewhere in the migration to a vm the server lost connectivity to the domain and it's network category changed from Domain to Public.

Firewall rules blocked access to SQL Server from public networks and subsequently blocked requests to connect, generating error #3 above.

The fix was to move the server back to a workgroup and then rejoin the domain. That action caused the network category to change back to Domain. At that point people were able to connect to the db server from the domain.