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?
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)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
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
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
Exception has been thrown by the target of an invocation.The wait operation timed out
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.