none
SqlConnection.Open() - Timeout expired RRS feed

  • Question

  • We are running SQL 2008 R2.  In the event log, I sometimes see this error when our  various .net web apps try to open a SQL Connection:

     

    StackTrace:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

       at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

       at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

       at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()

       at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)

       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)

       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)

       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)

       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

       at System.Data.SqlClient.SqlConnection.Open()

    Here is an example of our connect string:
    server=myserver;database=MyDB;Application Name=API;integrated security=SSPI;persist security info=False;Trusted_Connection=Yes;
    I suspect that we may have forgotten exhausted the connection pool.  Why this would happen is unclear (default pool size to small, forgot to close a sql connection/sql reader).    Is there a good method for tracking this down further?  It feels like looking for a needle in a haystack.
    thanks

     

    Friday, January 20, 2012 5:31 PM

Answers

  • Hi,

    The exception information is still too general for us to find out the root cause.  The timeout issue can be also caused by the database server.   I would like to recommend you check the server side as well, to see whether the database server is able to accept enough connections from different clients. 

    Good day!

    Thanks 


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by scott_m Tuesday, January 24, 2012 11:42 PM
    Tuesday, January 24, 2012 4:05 AM
    Moderator
  • Started reading an excellent book by Redgate Books: Performance Tuning with Sql Server Dynamic Management Views (Davidson & Ford).

     

    They have a nice script that shows your connections to SQL by app / machine.  This helps narrow down connection pool hogs:

     

    SELECT  dec.client_net_address ,

            des.program_name ,

            des.host_name ,

          --des.login_name ,

            COUNT(dec.session_id) AS connection_count

    FROM    sys.dm_exec_sessions AS des

            INNER JOIN sys.dm_exec_connections AS dec

                           ON des.session_id = dec.session_id

    GROUP BY dec.client_net_address ,

             des.program_name ,

             des.host_name 

          -- des.login_name

    -- HAVING COUNT(dec.session_id) > 1

    ORDER BY des.program_name,

             dec.client_net_address ;

    • Marked as answer by scott_m Wednesday, January 25, 2012 3:53 AM
    Wednesday, January 25, 2012 3:53 AM

All replies

  • Hi,

    You can use SQL Server profiler to see the connections on the database.   Does your app throw any Sql related exceptions?   Only from the callstack, we cannot say for sure where the root cause is.  But as you said, the connection pool is likely the problem here.  I would recommend you close the connection once you have finished using it.  

    Besides, please provide us with more information about your app. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 23, 2012 3:00 AM
    Moderator
  • Asp.net MVC (.NET 4.0) web app running in IIS 7.5.  Here is a better example of the exceptions we occassionally see:

     

    TypeName: System.Data.SqlClient.SqlException

    TypeAssembly: System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

    Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Source: .Net SqlClient Data Provider

    TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)

    StackTrace:    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

       at System.Data.SqlClient.SqlConnection.Open()

       at CT.iMagio2.Dal.Documents.Impl.DocumentDal.GetSearch(Int32 searchId)

    • Marked as answer by scott_m Tuesday, January 24, 2012 11:41 PM
    • Unmarked as answer by scott_m Tuesday, January 24, 2012 11:42 PM
    Monday, January 23, 2012 3:03 PM
  • Hi,

    The exception information is still too general for us to find out the root cause.  The timeout issue can be also caused by the database server.   I would like to recommend you check the server side as well, to see whether the database server is able to accept enough connections from different clients. 

    Good day!

    Thanks 


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by scott_m Tuesday, January 24, 2012 11:42 PM
    Tuesday, January 24, 2012 4:05 AM
    Moderator
  • Started reading an excellent book by Redgate Books: Performance Tuning with Sql Server Dynamic Management Views (Davidson & Ford).

     

    They have a nice script that shows your connections to SQL by app / machine.  This helps narrow down connection pool hogs:

     

    SELECT  dec.client_net_address ,

            des.program_name ,

            des.host_name ,

          --des.login_name ,

            COUNT(dec.session_id) AS connection_count

    FROM    sys.dm_exec_sessions AS des

            INNER JOIN sys.dm_exec_connections AS dec

                           ON des.session_id = dec.session_id

    GROUP BY dec.client_net_address ,

             des.program_name ,

             des.host_name 

          -- des.login_name

    -- HAVING COUNT(dec.session_id) > 1

    ORDER BY des.program_name,

             dec.client_net_address ;

    • Marked as answer by scott_m Wednesday, January 25, 2012 3:53 AM
    Wednesday, January 25, 2012 3:53 AM
  • Thanks for sharing!  :)  It's very helpful!

    Good day!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 25, 2012 5:33 AM
    Moderator
  • Dear,

    IF i have a large connections how to avoid timeout problem.

    Wednesday, August 29, 2012 6:58 AM