Timeouts when using pooling RRS feed

  • Question

  • We originally disabled connection pooling for our app because it was causing problems, but we now want to switch it back on. However, as soon as we do, all connections from our web app fail to connect and timeout.

    I'm thinking that the problem is either:
    1) a datareader not being closed properly somewhere, or
    2) the way in which the connection is instantiated

    So for 1, I recently went through the entire business layer to ensure that all SqlDataReaders are created using SqlCommand.ExecuteReader(CommandBehavior.CloseConnection) and then using .Close() on the datareader when reading is finished. There could be one or two left over somewhere which do not get closed properly, but I have no idea how to identify the offending code. Is there an easy way? Do you need to close anything when using data adapters or do they close automatically?

    For 2, the problem could be that we have an autofailover connection property in the data layer which will flick over to the mirror database server automatically if it cannot connect to the primary. This is the code:

    Public Shared ReadOnly Property Connection() As SqlConnection
            If String.IsNullOrEmpty(_strDefaultConnectionString) = True Then
                Throw New PropertyNotSetException("Cannot call Connection() before ConnectionString has been set")
            End If
            For s As Integer = 1 To 3
                Dim cd As SqlConnection = CreateConnection()
                If cd IsNot Nothing Then Return cd
            Throw New Exception("Too many connection failures while trying to connect")
        End Get
    End Property
    Private Shared Function CreateConnection() As SqlConnection
        Dim cnConnection As New SqlConnection(ActiveConnectionString)
            Return cnConnection
        Catch ex As Exception
                ' Send error email to support
                Dim objSmtp As New SmtpClient(Config.SmtpServer, Config.SmtpPort)
                Dim objEmail As New MailMessage("", "")
                objEmail.Subject = "Connection Error"
                objEmail.Body = String.Format("Error connecting to database using connection string ""{0}"", switching to connection string ""{1}"".{2}Error message: {3}", _
                                              _strActiveConnectionString, If(ActiveConnectionString.Equals(_strDefaultConnectionString), _strMirrorConnectionString, _strDefaultConnectionString), _
                                              Environment.NewLine, ex.Message)
                ' Todo: log to system log
            End Try
            _strActiveConnectionString = If(ActiveConnectionString.Equals(_strDefaultConnectionString), _strMirrorConnectionString, _strDefaultConnectionString)
            _strActiveSqlXmlConnectionString = If(ActiveSqlXmlConnectionString.Equals(_strDefaultSqlXmlConnectionString), _strMirrorSqlXmlConnectionString, _strDefaultSqlXmlConnectionString)
            Return Nothing
        End Try
    End Function
    The only problem I can think of is that the two methods are static, however they do instantiate and return a new SqlConnection object, which I assume would be fine?

    Anyone have any ideas on how to fix this? If we set Pooling=false in the connection string then everything works fine (if a little slow).
    Monday, September 14, 2009 8:34 AM


All replies