Sandip's Programming Zen

An attempt to share tech/coding experiences

Solution: Timeout expired – Connection Pool Error

with 8 comments

“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.”

How many times you have got above error? I have got it number of time but I was not troubled much as I did last time.

We have done following to resolve error (in sequence):

  1. Tried to check obvious code if we can find faulty code.
  2. Ran SQL profiler on Live scenario and gather as much data as possible
  3. Checked SQL server log for possible error messages.
  4. Checked Eventviewer for application error messages.
  5. Tried increasing connection pool value in web.config

None of above helped in our case at last we checked sql locks in syslocks table in master database which continuously holding lock on one of our primary tables. Some how lock was created and never removed during a simple update query, normally sql server creates lock on table when any insert/update/delete transaction is in progress.
We commented that update query and locking issue is gone and everything worked perfectly, no time out errors now.

As of now we don’t have any idea why a simple update query which was working normally, suddenly creating a permanent lock on a table, if I will find out with the help of my team will update here.

I suggest if you ever encounter this error try to follow the steps in the sequence I have mentioned above and narrow down the possibilities.

Update: If you want to set timeout property in DTS package than you can use timeout property shown in the screenshot below:

 

About these ads

Written by Sandip

January 29, 2008 at 7:06 pm

8 Responses

Subscribe to comments with RSS.

  1. If you are facing time out because your query or stored procedure is heavy and takes more time then usual then you can manually set timeout value on your command object, like this…

    objCmd.CommandTimeOut = 100

    Sandip

    February 13, 2008 at 9:40 am

  2. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Which setting I have to do?
    In sql server 2005 sp executing in 45 sec.

    i have use sqlhelper class

    SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & “GetCostSummaryByDate”, PickupFromDate, PickupToDate, CompanyNo, SystemNo, AccountNo, Reference, sort

    i have not use sqlcommand
    pls reply me as soon as possible

    regards,
    ANKIT CHAMPANERIYA

    ankit

    June 11, 2008 at 12:45 pm

  3. Hi Ankit,

    If you are using sql helper class then your command object must be in your helper class itself, so you have to set timeout property over there.

    But I advice you to check your tsql using Sql Profiler tool to find out duration/reads of your tsql. most probably you will find a problem in your tsql and fixing that will solve your timeout issues.

    Sandip

    June 11, 2008 at 12:57 pm

  4. error:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    In sql server 2005 sp executing in 45 sec.

    reference add: Microsoft.ApplicationBlocks.Data.dll
    code behind:
    i have use sqlhelper class

    SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & “GetCostSummaryByDate”, PickupFromDate, PickupToDate, CompanyNo, SystemNo, AccountNo, Reference, sort)

    i have not use sqlcommand

    pls reply me solution as soon as possible..

    Regards,
    ANKIT CHAMPANERIYA

    ankit

    June 11, 2008 at 1:07 pm

  5. the error is timeout expire is coming in client port, we are using DHCP server, using sql database server, give salution to slove this error

    sathish

    August 8, 2008 at 12:20 pm

  6. now i am using sql 2005 actually i get value from sql 2005 more that thousand records finding that time i will face time out expired how it slove pls advice on this

    Srini

    November 27, 2008 at 4:44 am

  7. Hi Srini,

    First I suggest do not load large number of records at the same time (use paging).
    If its required anyway then you can increase the “timeout property” of command object to the certain number appropriate in your scenario. It should solve your problem.

    Sandip

    November 27, 2008 at 6:34 am

  8. A Good post, I will be sure to bookmark this in my Newsvine account. Have a awesome day.

    Rocky Becton

    June 14, 2010 at 9:28 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: