Solution: Timeout expired – Connection Pool 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.”

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: