Thursday, February 21, 2008

Timeout expired error in ASP .NET

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.
Had you seen this Timeout expired error in your application before?
I have an application which will do a lot of database reads and updates. While the application is running to update records into database, suddenly I am getting this error.

I tried to add max pool size to a bigger size (Max Pool Size, default = 100), but in the end I still getting the same error, so I suspect that I am leaking connections.

public object Execute(string SQLStmt)
{
   try {
       m_cmd = new SqlCommand(SQLStmt, Connection);
       object a = m_cmd.ExecuteNonQuery();
       m_cmd.Dispose();
       return a;
   }
   catch (Exception e) {
       m_oErr = e;
       return null;
   }
}


Note that if my ExecuteNonQuery() throws an exception, the Dispose() will never get called. And after running a few hundreds of query, this timeout exception will be seen.

Obviously, I need to make sure that the Dispose() gets called even an exception has thrown. I had added finally block after the catch block.

public object Execute(string SQLStmt)
{
   try {
       m_cmd = new SqlCommand(SQLStmt, Connection);
       object a = m_cmd.ExecuteNonQuery();
       //m_cmd.Dispose();
       
return a;
   }
   catch (Exception e) {
       m_oErr = e;
       return null;
   }
   finally {
       m_cmd.Dispose();
   }
}


So even the ExecuteNonQuery() throws an exception, the code in the finally block still will get called. Definitely this solves my problem.

For more detailed information, you can refer to this article - Connection Pooling and the "Timeout expired" exception FAQ .

2 comments:

Anonymous said...

I will not approve on it. I assume warm-hearted post. Specially the title attracted me to read the intact story.

Anonymous said...

Amiable dispatch and this fill someone in on helped me alot in my college assignement. Thanks you as your information.

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template