Hello, I am experiencing a POOL connection limit overflow, While I am monitoring the connections in the performance monitor I see connections rising while users connect to our webshop. Restarting IIS, or restarting the application, of restarting SQL does not
reset the number of pool connections. How can these connections be forced to zero with the OS Using win200 server. TNX
Is your application set up to properly use connection pooling? Here are the rules that I picked up off a site:
Quick Tips On Connection Pooling. Be sure that your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the
connection string is different, then a new connection will be opened. Only open a connection when you need it, not before. Close your connection as soon as you are done using it. Don't leave a connection open if it is not being used. Be sure to drop any temporary
objects before closing a connection. Be sure to close any user-defined transactions before closing a connection. Don't use application roles if you want to take advantage of connection pooling. Here's a link to MS on Connection Pooling:
MS Article.
Hello, TNX for the response.... I am aware of all this, But, my question was how to reset the pool connections without restarting the server..... I found that restarting the server is the only way to free up the pool connections. Is there a way to do this without
restarting the whole server ??? TNX
I guess my assumption was tied to thinking that your db connections were not being pooled properly, therefore causing the overflow. As far as reseting the connection pool from code I am not sure how to do that. Wish I had more info for you.
When I run into this problem, I usually cheat by using the "Detach Database" tool. To do this, right-click on your database, go to All Tasks -> Detach Database. The dialog that pops up has a "Clear" button that clears open connections to that database. Once
done, just cancel out of that dialog box (if you don't cancel, the database will get detached and you'll have to screw around with MSSQL to get it back and running. If this happens, check Books Online to find out how to get it back). Sometimes it takes a while
(couple minutes) for everything to get reset. Meaning, you may get a "General Network" error when trying to reattach to that database. If I remember correctly, an iisreset will solve this. Keep in mind that I do this on my own development server... Dallas
I found that the reason for connection pool overflows has to do with the use of the
System.Data.SqlClient.SqlDataReader.ExecuteReader(CommandBehavior.CloseConnection) method with the CloseConnection parameter set. If you use ExecuteReader and then dont actually bind the reader to a web form control, (e.g if you just do a .read() on
the reader object and set some variables equal to the results, then the reader doesn't actually close as you might expect. You have to explicitly close it in this circumstance. Here's some code examples: HERES THE CODE FOR OPENING THE CONNECTION:
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim objDR As SqlDataReader
Dim strConn =
Dim strSQL =
objConn.ConnectionString = strConn
objConn.Open()
objCmd.Connection = objConn
objCmd.CommandText = strSQL
objDR = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
THE FOLLOWING CODE WILL CAUSE THE CONNECTION POOL TO EVENTUALLY OVERFLOW:
If objDR.Read Then
intArticleID = objDR.Item("ID")
strTitle = objDR.Item("Title")
intPostedBy = objDR.Item("PostedBy")
intPublishStatus = objDR.Item("Status")
End If
BECAUSE objDR never reaches the end of the recordset, and the CommandBehavior.CloseConnection doesn't actually "fire" as you might expect. Causing the connection pool to keep growing out of control every time this code runs.
SO THIS WILL CAUSE PROBLEMS, UNLESS YOU ADD "objDR.Close()" AFTER GETTING THE VALUES YOU NEED FROM THE RECORD SET.
THE FOLLOWING CODE WILL NOT CAUSE THE CONNECTION POOL TO OVERFLOW:
BECAUSE
IN THIS INSTANCE, objDR is actually Bound to the objDropdown object, which causes the CommandBehavior.CloseConnection parameter to automatically close the connection when it reaches the end of the recordset. THEREFORE, you MUST use the .Close() method of the
objDR datareader (and thus, the associated connection) unless you are using the objDR to bind to a form control, in which case the connection WILL close automatically, assuming you have set CommandBehavior.CloseConnection parameter in the opening of the objDR
datareader.
easoft
Member
35 Points
7 Posts
Connection pool reset...
Oct 28, 2003 08:14 AM|LINK
PD_Goss
Contributor
2310 Points
460 Posts
Re: Connection pool reset...
Oct 28, 2003 09:47 AM|LINK
easoft
Member
35 Points
7 Posts
Re: Connection pool reset...
Oct 29, 2003 11:17 AM|LINK
PD_Goss
Contributor
2310 Points
460 Posts
Re: Connection pool reset...
Oct 30, 2003 10:22 AM|LINK
dallas.net
Member
20 Points
4 Posts
Re: Connection pool reset...
Oct 31, 2003 02:58 AM|LINK
rcoopman
Member
45 Points
9 Posts
Re: Connection pool reset...
Dec 08, 2003 09:32 PM|LINK
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim objDR As SqlDataReader Dim strConn = Dim strSQL = objConn.ConnectionString = strConn objConn.Open() objCmd.Connection = objConn objCmd.CommandText = strSQL objDR = objCmd.ExecuteReader(CommandBehavior.CloseConnection)THE FOLLOWING CODE WILL CAUSE THE CONNECTION POOL TO EVENTUALLY OVERFLOW:If objDR.Read Then intArticleID = objDR.Item("ID") strTitle = objDR.Item("Title") intPostedBy = objDR.Item("PostedBy") intPublishStatus = objDR.Item("Status") End IfBECAUSE objDR never reaches the end of the recordset, and the CommandBehavior.CloseConnection doesn't actually "fire" as you might expect. Causing the connection pool to keep growing out of control every time this code runs. SO THIS WILL CAUSE PROBLEMS, UNLESS YOU ADD "objDR.Close()" AFTER GETTING THE VALUES YOU NEED FROM THE RECORD SET. THE FOLLOWING CODE WILL NOT CAUSE THE CONNECTION POOL TO OVERFLOW:objDropdown.DataSource = objDR objDropdown.DataTextField = "Title" objDropdown.DataValueField = "ID" objDropdown.DataBind()BECAUSE IN THIS INSTANCE, objDR is actually Bound to the objDropdown object, which causes the CommandBehavior.CloseConnection parameter to automatically close the connection when it reaches the end of the recordset. THEREFORE, you MUST use the .Close() method of the objDR datareader (and thus, the associated connection) unless you are using the objDR to bind to a form control, in which case the connection WILL close automatically, assuming you have set CommandBehavior.CloseConnection parameter in the opening of the objDR datareader.