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!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan McFaddenSystems EngineerCommented:
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
0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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

0
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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!
0
Dan McFaddenSystems EngineerCommented:
220 users during an hour is not much activity.  You would have to analyze your http logs to get a better idea of exactly happens (happened) during this timeframe.

You might want to change the connecting pooling configuration on your connection string.  Increase the "Max Pool Size" may help.  But try not to over do it.

Here is an MSDN article about connection pools and connection string configuration options.  At the bottom is a list of available configuration options.

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

Here's a link about Connection Pooling and SQL Server together.

Link:  https://msdn.microsoft.com/en-US/library/8xx3tyca(v=vs.110).aspx

I would test various connection string configs and monitor the effect on your server.

As for load balancing... I can't immediately say yes or no.  It would be best to better understand your application's usage patterns, this means analyzing http logs and performance logs over a long timeframe to see the net effect of your app on the server.

Inserting a load balancer in front of your web app is only useful if you want to distribute traffic across a server farm.  Load balancing in general solves nothing when there is only 1 server in operation.

An even it load balancing is warranted, you would still potentially have this issue as http traffic increases on the app.

So my recommendation is to do thru the MSDN docs about managing connections to dbs in code and to test out various connection string configurations that tweak the connection pool config.

Dan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.