0

I have an exe which gives the below error.

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.

I have used using the statement to create a connection object and also close the connection in finally block.

In web.config of exe connection string, maximum app_pool size value is 200.

I also checked the connection using the sp_who2 command, this command shows 25 connections.

private Dictionary<string, SomeObject> SomeMethod(int orderedXmlId, string clientLogFilePath, ref bool errorGettingRMData)
        {
            Dictionary<string, SomeObject> someDictionary = new Dictionary<string, SomeObject>();
            //Create connection object
            using (SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["MasterDB"]))
            {
                SqlCommand command = null;
                try
                {
                    //open the connection
                    connection.Open();
                    command = new SqlCommand("someSP", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@someParameter", someParameter);
                    command.CommandTimeout = _commandTimeOut;

                    using (SqlDataReader someVariable = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (someVariable != null && someVariable.HasRows)
                        {
                            int intId = someVariable.GetOrdinal("intId");
                            int imageUrl = someVariable.GetOrdinal("imageUrl");
                            int contentLength = someVariable.GetOrdinal("contentLength");
                            int path = someVariable.GetOrdinal("path");
                            int parentId = someVariable.GetOrdinal("parentId");
                            int propertyId = someVariable.GetOrdinal("propertyId");

                            while (someVariable.Read())
                            {
                                using (SomeObject someUploadObject = new SomeObject())
                                {
                                    // Avoid this Exception: "An item with the same key has already been added".
                                    if (!someDictionary.ContainsKey(someVariable.GetString(intImageUrlOrdinal)))
                                    {
                                        someUploadObject.OrderedXmlId =
                                            someVariable.GetInt32(intOrderedXMLIdOrdinal);
                                        someUploadObject.ImageUrl = someVariable.GetString(intImageUrlOrdinal);
                                        someUploadObject.ContentLength =
                                            someVariable.GetInt64(intContentLengthOrdinal);
                                        someUploadObject.SaveAsPath = someVariable.GetString(intSaveAsPathOrdinal);
                                        someUploadObject.ParentOrderedXmlId =
                                            someVariable.GetInt32(intParentOrderedXmlIdOrdinal);
                                        someUploadObject.PropertyId = someVariable.GetString(intPropertyIdOrdinal);

                                        //Add to Dictionary
                                        someDictionary.Add(someUploadObject.ImageUrl, someUploadObject);
                                    }
                                    else
                                    {
                                        if (detailedLog)
                                        {
                                            AddToLog("SomeMethod: Image {0}, for id:{1} is already present in someDictionary",
                                                                     new object[] { someVariable.GetString(imageUrlOrdinal), lId }, 80,
                                                                     false, true, clientLogFilePath);
                                        }
                                    }
                                }
                            }
                            someVariable.Close();
                        }
                        //No records are found i.e. Datareader is empty
                        else
                        {
                            //Do nothing
                        }
                    }
                }
                catch (Exception ex)
                {
                    errorGettingData = true;
                    Logger.Write2Log("SomeMethod: Error in retrieving uploaded images for id:" + Id + "-" + ex.Message, true, 40, true, logFilePath);
                    SendMail(MailOption.exception, ex, logFilePath);
                }
                finally
                {
                    command = null;
                    if (connection != null)
                    {
                        connection.Close();
                    }
                }
                //Return list of Dictionary
                return someDictionary;
            }
        } //SomeMethod

Any idea what might have gone wrong here?

Swapnil Khanorkar
  • 23
  • 1
  • 1
  • 8
  • 1
    would you mind showing us some of the code where you are disposing of your objects? – Floyd1256 Feb 15 '18 at 07:22
  • Just FYI, If you are using `using` statement, then you don't have close the connections. – Jaliya Udagedara Feb 15 '18 at 07:22
  • Possible duplicate of [How can I solve a connection pool problem between ASP.NET and SQL Server?](https://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server) – VDWWD Feb 15 '18 at 07:43
  • Or https://stackoverflow.com/questions/23731876/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from – VDWWD Feb 15 '18 at 07:43
  • Or https://stackoverflow.com/questions/1642252/the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool – VDWWD Feb 15 '18 at 07:44
  • thanks all for the reply – Swapnil Khanorkar Feb 15 '18 at 07:52

1 Answers1

2

Well, the error is self descriptory. Start with the usual suspects:

  1. Check if your server is really using the config you intended(as opposed to one that has a limit of 25 connections). See if problems really start at that number.
  2. If possible, check the pool directly - if it's the connections you've made, that are being hogged, then you are not discarding them correctly-there is usually a pattern how to handle this. Use it, don't reinvent the wheel.
G F
  • 21
  • 3