Connection Timeouts - a tough one!

I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.

I'm using the following db connection string:
Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;

As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.

The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

After monitoring and debugging, I've found two things.

1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.

2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?

I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)

Any help is deeply appreciated!Hi,
I'm facing similar problem as well on my local machine (Windows 2000 advance server + SQL Server 7.0). The difference is that when i check the number of connections, they had reached the max size of 100.
I think my problem lies in connections that remain open after the page is loaded. For example whenever I run a particular page(that shows a complex report), 14 more connections are added to User Connections in Performance Monitor.
Can anyone tell me why the connections remain open even after the page is executed? Shouldn't they be closed once the page is loaded and returned to the connection pool to be reused?

Any help/suggestion will be highly appreciated.

Thanks|||The common reason for the problems you are having (often referred to as "connection leakage") is that the database connection object is not closed after the page is finished using it. If you don't close the object manually, it won't be returned to the pool until it times out.

Example of syntax:

Dim objConn As New SqlConnection( ConnectionString )

objConn.Open()
' put code to communicate with database here
objConn.Close()

If you are sure that you are closing all connections properly, and still are experiencing this problem, I'm all out of ideas..