locked
Connection Pool Leaks / Errors RRS feed

  • Question

  • User-783490318 posted

    Hello,

    We are experiencing very odd behavior in an app relating to connection issues. I'm not certain if the problem is with ASP.NET or SQL Server but I figured I'd start here.

    This is a very DB-intensive app and uses a combination of strongly typed dataset / table adapter setup as well as SqlConnection / SqlCommand objects for the data access. The problem is that the connection pool seems to grow out of control and this results in timeout errors when trying to get a connection. The specific error message is:

    "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."

    and here is a copy of the stack trace:

    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 UserControls_CompanyNews.GetNews() at UserControls_CompanyNews.Page_Load(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    Previously I had set the connection pool - and tried various sizes - but that didn't seem to help; so I removed the explicit designation and reverted to the default.

    Now for the odd part... this has happened three time; once every six months - to the day! It happened 4/29/09, 10/18/09, and today (4/28/10). I don't believe that is just a coincidence.

    Any ideas?

    Thanks,

    Ryan

    Wednesday, April 28, 2010 5:06 PM

Answers

  • User-952121411 posted

    Now for the odd part... this has happened three time; once every six months - to the day! It happened 4/29/09, 10/18/09, and today (4/28/10). I don't believe that is just a coincidence.

    Since it is so preciously every 6 months, that makes me think some kind of server maintenance where your web app or more likely SQL Server instance is installed is occurring.  The networking or infrastructure team might have scripts running on 6 month intervals that temporarily stop SQL Server, reboot the server, restart IIS, etc.  Most of the maintenance issues I raised usually don't result in a connection pool error, but since it is so preciously every 6 months, somehting on schedule may be occuring.  If you can access the servers, take a look to the event log and look at the events logged just prior and after the connection pool error is generated. Also talk to the parties responsible for the server, and see if they know anything occurring regularly every 6 months on those dates.  It may indicate a process that has launched and interfering with your app.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 30, 2010 3:11 PM

All replies

  • User214117797 posted

    The most common cause of this could be that you are not disposing of the connection and there are open connections laying around that are not being returned to the pool.


    Wednesday, April 28, 2010 6:57 PM
  • User-783490318 posted

    True but I am explicity closing all connections that I manually open. As for the data access via the DataSet I followed the following tutorial so I presume that is all handled automatically: http://www.asp.net/learn/data-access/tutorial-01-vb.aspx.

    Ryan

    Wednesday, April 28, 2010 8:54 PM
  • User214117797 posted

    True but I am explicity closing all connections that I manually open

    How are you disposing your connections?

    Are you using the "using" construct or calling Dispose/Close in your finally clause?

    Thursday, April 29, 2010 3:32 AM
  • User-783490318 posted

    I'm using the using directive; see the following example:

        Public Function GetClientOpenItems(ByVal client_id As Integer) As DataTable
            Dim dt_ As New DataTable
            Dim da_ As New SqlDataAdapter
    
            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("tpConnectionString").ToString())
                Using command As New SqlCommand()
                    command.CommandText = "Billing_GetClientOpenItems"
                    command.CommandType = CommandType.StoredProcedure
                    command.Connection = conn
                    command.Parameters.AddWithValue("@client_id", client_id)
    
                    conn.Open()
                    da_.SelectCommand = command
                    da_.Fill(dt_)
                    conn.Close()
                End Using
            End Using
    
            Return dt_
        End Function


    Now, for the DataSet / TableAdapter code as mentioned in the above tutorial, here is a snippet of that:

        Private _clientsAdapter As ClientsTableAdapter = Nothing
        Protected ReadOnly Property Adapter() As ClientsTableAdapter
            Get
                If _clientsAdapter Is Nothing Then
                    _clientsAdapter = New ClientsTableAdapter()
                End If
    
                Return _clientsAdapter
            End Get
        End Property
        <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetClients() As Titleprep.ClientsDataTable
            Return Adapter.GetClients
        End Function


    And here is the ClientsTableAdapter.GetClients function (from MetaData):

            <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
             Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
             Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.[Select], true)>  _
            Public Overloads Overridable Function GetClients() As Titleprep.ClientsDataTable
                Me.Adapter.SelectCommand = Me.CommandCollection(0)
                Dim dataTable As Titleprep.ClientsDataTable = New Titleprep.ClientsDataTable
                Me.Adapter.Fill(dataTable)
                Return dataTable
            End Function


    Thursday, April 29, 2010 8:17 AM
  • User-783490318 posted

    I'm using the using directive; see the following example:

        Public Function GetClientOpenItems(ByVal client_id As Integer) As DataTable
            Dim dt_ As New DataTable
            Dim da_ As New SqlDataAdapter
    
            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("tpConnectionString").ToString())
                Using command As New SqlCommand()
                    command.CommandText = "Billing_GetClientOpenItems"
                    command.CommandType = CommandType.StoredProcedure
                    command.Connection = conn
                    command.Parameters.AddWithValue("@client_id", client_id)
    
                    conn.Open()
                    da_.SelectCommand = command
                    da_.Fill(dt_)
                    conn.Close()
                End Using
            End Using
    
            Return dt_
        End Function


    Now, for the DataSet / TableAdapter code as mentioned in the above tutorial, here is a snippet of that:

        Private _clientsAdapter As ClientsTableAdapter = Nothing
        Protected ReadOnly Property Adapter() As ClientsTableAdapter
            Get
                If _clientsAdapter Is Nothing Then
                    _clientsAdapter = New ClientsTableAdapter()
                End If
    
                Return _clientsAdapter
            End Get
        End Property
        <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _
        Public Function GetClients() As Titleprep.ClientsDataTable
            Return Adapter.GetClients
        End Function


    And here is the ClientsTableAdapter.GetClients function (from MetaData):

            <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
             Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
             Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.[Select], true)>  _
            Public Overloads Overridable Function GetClients() As Titleprep.ClientsDataTable
                Me.Adapter.SelectCommand = Me.CommandCollection(0)
                Dim dataTable As Titleprep.ClientsDataTable = New Titleprep.ClientsDataTable
                Me.Adapter.Fill(dataTable)
                Return dataTable
            End Function


    Thursday, April 29, 2010 8:18 AM
  • User214117797 posted

    What is the security context of your connection strings? By that I mean is the connection string exactly the same for each connection.

    What is the approximate database hits, you may need to increase the pool size.

    Try logging some performance counters on ADO.NET to see if the pool is running out etc

    Thursday, April 29, 2010 6:33 PM
  • User-783490318 posted

    The security is the same for all  - the connection is via a single SQL Server login - not a windows user and/or varying login for each user. There are approximately 15-30 concurrent users so it's not a huge app; it's very DB intensive, but not a large user base.

    I can try the performance counters but since this happens so rarely it will be hard to debug.

    What do you think about the fact that this happens exactly every 6 months? I don't think that is a coincidence, seems very unlikely.

    Thanks for your help!

    Thursday, April 29, 2010 7:46 PM
  • User214117797 posted

    There are approximately 15-30 concurrent users so it's not a huge app; it's very DB intensive, but not a large user base.

    Is it possible for each of those connections while performing the database activity take a long time to complete?

    I can try the performance counters but since this happens so rarely it will be hard to debug.

    I think this is the main issue, where you cannot replicate it in a manner where you can then enable sql profiling.

    What do you think about the fact that this happens exactly every 6 months? I don't think that is a coincidence, seems very unlikely.

    It seems like you are disposing the connection, so the only probable cause could be the time it takes for your database operation to complete is not freeing up enough connections for queued up requests.

    Are there any major database transactions that occur at that 6 month interval?


    Thursday, April 29, 2010 8:30 PM
  • User-783490318 posted

    I would say 98-99% of all queries are simple selects that take a fraction of a second to execute. There are some reports that are generated that could take 20-30 seconds to execute. However, there is only one user that would be running those reports and I know he wasn't running any when the problem happened.

    Could you point me to a good tutorial on how to setup some performance counters and monitor? I know how to do that via the MMC snap-in on the server, but is there a way to log that information for a couple days?

    There are no major DB transactions occuring every six months. The only automated process is invoice generation. This occurs on the 1st of the month and we only process 10 at a time so that isn't the issue.

    Thanks,

    Ryan

    Friday, April 30, 2010 11:33 AM
  • User-952121411 posted

    Now for the odd part... this has happened three time; once every six months - to the day! It happened 4/29/09, 10/18/09, and today (4/28/10). I don't believe that is just a coincidence.

    Since it is so preciously every 6 months, that makes me think some kind of server maintenance where your web app or more likely SQL Server instance is installed is occurring.  The networking or infrastructure team might have scripts running on 6 month intervals that temporarily stop SQL Server, reboot the server, restart IIS, etc.  Most of the maintenance issues I raised usually don't result in a connection pool error, but since it is so preciously every 6 months, somehting on schedule may be occuring.  If you can access the servers, take a look to the event log and look at the events logged just prior and after the connection pool error is generated. Also talk to the parties responsible for the server, and see if they know anything occurring regularly every 6 months on those dates.  It may indicate a process that has launched and interfering with your app.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 30, 2010 3:11 PM
  • User-783490318 posted

    Thanks, that's what I'm inclined to think as well.

    It is a dedicated server at Rackspace so I'll look through the event log and also touch base with them to see if they have anything running every six months. I spoke with them after the 2nd time but I didn't realize the 6-month recurrence at that point.

    Monday, May 3, 2010 8:22 AM