Connections in Pool Not Being Reused

I have a form that has a number of database calls. It does these sequentially. In some cases it's not possible (due to archetecture more than anything) to make these calls all on the same connection. However it does open then close the connection. (all connections created are in using statements)

What I'm seeing is that we're getting a timeout when the 100 connection limit is hit. Looking at Activity Monitor I can see these connections growing and growing over time in the activity monitor the more windows you open and close even though these connections are closed. (and they all use exactly the same connection string)

I read that it might be because of SQL Debugging being turned on in vs.net, but that is not enabled in either debug or runtime for us and we're seeing it in client sites as well as locally.

We're running .NET 2.0.

Does anyone have any ideas as to why it won't reuse these clearly properly closed connections?

Thanks!

Does it happen even with a connection alias ? For more information, please refer http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx|||Are the connections using the exact same connection string so you can take advantage of connection pooling? Even with a using block, you might want to try to explicitly close the connection.|||

Yes, they're using the exact same connection string, and yes I tried explicitly closing the connection and while it shows as waiting instead of inuse in the Activity monitor after doing that, they will not be recycled. If I dispose of the form that the connection is on (which is a custom drop down for a custom combo control) then it gets released and the connection gets reused.

I'm not using conneciton aliases. I have yet to see the point.

Connections in Pool Not Being Reused

I have a form that has a number of database calls. It does these sequentially. In some cases it's not possible (due to archetecture more than anything) to make these calls all on the same connection. However it does open then close the connection. (all connections created are in using statements)

What I'm seeing is that we're getting a timeout when the 100 connection limit is hit. Looking at Activity Monitor I can see these connections growing and growing over time in the activity monitor the more windows you open and close even though these connections are closed. (and they all use exactly the same connection string)

I read that it might be because of SQL Debugging being turned on in vs.net, but that is not enabled in either debug or runtime for us and we're seeing it in client sites as well as locally.

We're running .NET 2.0.

Does anyone have any ideas as to why it won't reuse these clearly properly closed connections?

Thanks!

Does it happen even with a connection alias ? For more information, please refer http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx|||Are the connections using the exact same connection string so you can take advantage of connection pooling? Even with a using block, you might want to try to explicitly close the connection.|||

Yes, they're using the exact same connection string, and yes I tried explicitly closing the connection and while it shows as waiting instead of inuse in the Activity monitor after doing that, they will not be recycled. If I dispose of the form that the connection is on (which is a custom drop down for a custom combo control) then it gets released and the connection gets reused.

I'm not using conneciton aliases. I have yet to see the point.

Connection problems

Basically I am getting this message every now and again: "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." But the weirdest thing is I am closing all my connections, and using a single connection string to my SQL Server database. Here is a typical function to prove this:

Public Shared Function GetTheMenu() As SqlDataReader

Dim connection As New SqlConnection(dbconnection.connString)
Dim command As New SqlCommand("SP_GetTheMenu", connection)

command.CommandType = CommandType.StoredProcedure
connection.Open()

Return command.ExecuteReader(CommandBehavior.CloseConnection)

End Function

When I go into SQL i just see loads of open connections to the database with references to the stored procedure that has been executed - surely this should not be the case if the stored procedure is a simple Select statement that is pases a value to to a datareader, and is (supposedly) automatically closed?

Can anyone suggest reasons why this is happening?

Thanks.

I have seen this error before. Have you tried calling Close() manually on the connection object?

Andy.

|||Yes and it makes no difference|||Also make sure to close all DataReaders after finishing using them. You can also try Dispose() connections after using them.|||Is there anything I can do at the database level to make sure the connection closes?|||

You can check the master..sysprocesses to find out current connections on the SQL Server. Please take a look at this post:

http://forums.asp.net/thread/1400183.aspx