DAAL gives "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may..." error and freeze the server

Topics: Data Access Application Block
May 23, 2007 at 11:35 PM
Hi,

I'm using DAAL 3.0 (and also used version 2) for a 100% uptime application that connects to a database (MSSQL 2000).
After a few hours of running the application, my MSSQL server freeze and a restart is required.

Here is a short description of my application's architecture:
In this application I have 10 System.Timers.Timer that run every 20, 30 or 60 sec, depends on the timer. Those timers are set to 'AutoReset = False' so they won't start again if they are still running.
I created one database object that I transfer to all functions in each timer. In these functions objects are being created. these objects create commands in the original database object.
The commands are being destroyed with the objects and recreate with the new objects when the timer elapsed.
In some of the timers there is only one access to the database (i.e Me.db.ExecuteNonQuery(Data.CommandType.StoredProcedure, "CloseEvents"), in other timers there are upto 10 different sp executions, the majority are ExecuteNonQuery, but some are ExecuteReader or ExecuteDataSet.

The error I get from the MSSQL server (before?) the freezing is "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." for each timer I run in the application.

When I check the database 'Activity Monitor' the time the application works OK, I can see only 3 processes with CPU: 2587,8985,2112 ; Physical IO: 167,499,102 ; Memory Usage: 6,14,0 accordingly and login times later than the time I started the application. 'Blocked By' and 'Blocking' on all are 0.

I used the defaults in the connection string (..., Max Pool Size = 100, Pooling = True,...). I have more application using the MSSQL server and even the same database. The general amount of users (Applications) that use the MSSQL server has not been change and there were times before running this application that more users were connected.

Any idea what is going on and what can I do to fix that?

Thanks a lot,

Alon
May 24, 2007 at 7:49 AM
It seems like you dont free your connections in your code.. How do the ExecuteReader code look like. Are u using "using IDataReader rst = db.ExecuteReader(cmd);"?
May 24, 2007 at 2:34 PM
Hi,

Yes, I'm using the 'Using' in all ExecuteReaders.

But I noticed that in 3 places that I use ExecuteDataset - I dont dispose the Dataset at the end, maybe this is the cause? anyway I added the dispose and will check it today.

I also put a sniffer for logging the maximum connections from my application to the server (currently the max was 7).

Thanks,

Alon