asked on

The timeout period elapsed prior to obtaining a connection from the pool.

I am getting this error a LOT during the day, during busy times:

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

Open in new window

My settings in the apppool have been customized, because they were resetting during the day and I was getting another error: Validation of viewstate MAC failed

I believe I had been told that it rotates on 27 hours automatically, so I wanted to avoid that and set it to run at 4:15 every morning instead.

My settings are:
Regular Time Interval: 0 instead of 1740.
Specific Times: 4:15 AM
Private memory limit: 0
Request Limit 0

Any suggestions on what it should be to avoid it resetting and losing people's sessions during the day? Yet not timing out now?

Help please?

thanks!

Dan McFadden

This could indicate that there is an issue in the web applications database connection management. If the connections to the database are not cleanly closed, what can happen is that the connections cannot be reused and your app exhausts the configured number of pool connections.

This results in the app waiting for a connection to become free but it never happens before the configured wait time expirers.

1. What language is the app written in?
2. What does your app's db connection string look like?

Here's an article that goes over the configuration and management of db connections (assumes ASP.NET).

Link: https://msdn.microsoft.com/en-US/library/ms254507(v=vs.80).aspx

Dan

Starr Duskk

ASKER

I either open and close like this (I've snipped out the field list):

    Public Sub Read(ByVal Id As Integer)
        Dim cmd As SqlClient.SqlCommand
        Dim params As SqlClient.SqlParameterCollection
        
        Try
            Clear()				
        
            ' Prepare the SqlCommand and SqlParameterCollection for calling LogReadById
            Dim sqlConnection As New SqlClient.SqlConnection(Name.GetConnectionString)
    
            cmd = New SqlClient.SqlCommand("[sss.LogRead]", sqlConnection)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 30
            params = cmd.Parameters	
            
            ' Register PK's as InputOutput SQL Parameter(s)
            params.Add("@LogId", SqlDbType.Int).Direction = ParameterDirection.InputOutput
            params("@LogId").Value = Id
            ' Register Non-PK's as Output SQL Parameter(s)
            params.Add("@IPAddress", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output
          
                    
            cmd.Connection.Open() 
            cmd.ExecuteNonQuery()
            cmd.Connection.Dispose()
            
            ' Fetch PK values to private members
            _logId = IntegerCheckNull(params("@LogId"))
            ' Fetch Non-PK values to private members IF a row was successfully returned
            If _logId <> INVALID_ID Then
               _iPAddress = StringCheckNull(params("@IPAddress"))
            End If
            
            cmd.Dispose()
    
        Catch ex As Exception
            Throw New BaseException(String.Empty, EventLogEntryType.Error, ex, Me)
        End Try 		
    End Sub

Open in new window

Or this:

       Dim sql As String = QueryString
            Dim con As New SqlConnection(Name.GetConnectionString)
            Dim cmd As New SqlCommand(sql, con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                InitializeRow(dr)
            End If
            If Not dr.IsClosed() Then
                dr.Close()
            End If
            dr.Dispose()
            con.Dispose()
            cmd.Dispose()

Open in new window

Or using the "using" blocks like this:

            Using con As New SqlConnection(Name.GetConnectionString)
                
                Dim cmd As SqlCommand = New SqlCommand("[sss.LogDelete]",con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandTimeout = 30
                Dim params As SqlParameterCollection = cmd.Parameters	
            
               ' Register PK's as Input SQL Parameter(s) for matching
               params.Add("@LogId", SqlDbType.Int).Value = _logId
                params.Add("@result", SqlDbType.SmallInt).Direction = ParameterDirection.Output

                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                
                ' 1 success
                '-1 failure
                '-2 entity is owned by other resource
                '-3 entity does not exist
                '-4 dirty read
                retval = ShortCheckNull(params("@result"))
    
            End Using ' using blocks automatically call Dispose() and close con

Open in new window

The app is written in vb.net 4.5 framework.

The connection string looks like this:

<add name="XConnectionString"
   connectionString="Data Source=xxx.xx.xx.xx;Initial Catalog=XXXXX;User Id=XXXX; password=XXXX"
providerName="System.Data.SqlClient" />

Open in new window

Starr Duskk

ASKER

Also, we had 220 users online during that hour (likely not all at once, but you never know). Would that be an issue?

Do we need a load balancer?

thanks!

ASKER CERTIFIED SOLUTION

Dan McFadden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial