SqlClient Connections created don't get reused in the pool and causeerror in ASP.NET Page

[mc]

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[Marina Levit [MVP]]

Connection pooling means that the pool manager keeps some 'open' if you are
looking in enterprise manager. Sounds odd that you are reaching 100.

However, are you getting error messages that no connections can be gotten
from the pool because they are all taken? If not, then it sounds like they
are being placed back in the pool properly. If the connections truly
remained open, after 100, you would no longer be able to open connections,
as it would say the pool is out of available ones.

 

[zackary.evans]

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

 

[Marina Levit [MVP]]

The 'using' statement disposes (which also closes) the connection once the
end of the using statement is reached.

I assume the line
using (SqlCommand innerConnect = DBHelper.GetConnection())
is supposed to be
using (SqlConnection innerConnect = DBHelper.GetConnection())

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[zackary.evans]

Cool, I didn't know that the connection was explicitly closed at the
end of the "using" connection statement.

Since this is managed by the CLR though, doesnt that imply that it will
only dispose of the resources when GC decides to run? I'm not sure how
he is testing his client but it might be possible this is causing the
problem....

The 'using' statement disposes (which also closes) the connection once the
end of the using statement is reached.

I assume the line
using (SqlCommand innerConnect = DBHelper.GetConnection())
is supposed to be
using (SqlConnection innerConnect = DBHelper.GetConnection())

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[Marina Levit [MVP]]

Well, calling the Dispose method, and cleaning up an object are two
different things. At the end of the 'using' statement, the Dispose method
gets called for that object. The implementation of SqlConnection happens to
call Close in its Dispose implementation.

The object itself might get garbage collected at some point way down the
line, but the connection should be closed once that 'using' statement is
done.

Cool, I didn't know that the connection was explicitly closed at the
end of the "using" connection statement.

Since this is managed by the CLR though, doesnt that imply that it will
only dispose of the resources when GC decides to run? I'm not sure how
he is testing his client but it might be possible this is causing the
problem....

The 'using' statement disposes (which also closes) the connection once
the
end of the using statement is reached.

I assume the line
using (SqlCommand innerConnect = DBHelper.GetConnection())
is supposed to be
using (SqlConnection innerConnect = DBHelper.GetConnection())

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.


mc wrote:
I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[mc]

I read in a KB article that if you use the using statement it will
invoke the dispose method of the Connection, it's the same thing as
using a try/finally block to ensure it gets done if any errors are thrown.

Have I misinterpreted this?

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[mc]

I get the error "Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have occurred because all
pooled connections where in use and max pool size was reached"

When I look at "Current activity > Process Info" in "SQL Server
Enterprise Manager". I see over 100 connections (17 other background etc
processes) one for each time I ran the code below. The status of all is
"sleeping" and the command is "AWAITING COMMAND"

Connection pooling means that the pool manager keeps some 'open' if you are
looking in enterprise manager. Sounds odd that you are reaching 100.

However, are you getting error messages that no connections can be gotten
from the pool because they are all taken? If not, then it sounds like they
are being placed back in the pool properly. If the connections truly
remained open, after 100, you would no longer be able to open connections,
as it would say the pool is out of available ones.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays open
(Process Info in Sql Server Enterprise Manager say the connection is
sleeping) and very soon I hit the currently defined 100 connections limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[mc]

Yea, typo induced when copy code from my development machine to the
internet.

The 'using' statement disposes (which also closes) the connection once the
end of the using statement is reached.

I assume the line
using (SqlCommand innerConnect = DBHelper.GetConnection())
is supposed to be
using (SqlConnection innerConnect = DBHelper.GetConnection())

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[Marina Levit [MVP]]

This means you have a connection leak.

Is this the only place in the entire application where you are using
connections?

I get the error "Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have occurred because all
pooled connections where in use and max pool size was reached"

When I look at "Current activity > Process Info" in "SQL Server Enterprise
Manager". I see over 100 connections (17 other background etc processes)
one for each time I ran the code below. The status of all is "sleeping"
and the command is "AWAITING COMMAND"

Connection pooling means that the pool manager keeps some 'open' if you
are looking in enterprise manager. Sounds odd that you are reaching 100.

However, are you getting error messages that no connections can be gotten
from the pool because they are all taken? If not, then it sounds like
they are being placed back in the pool properly. If the connections truly
remained open, after 100, you would no longer be able to open
connections, as it would say the pool is out of available ones.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection is
sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[Guest]

Could it be that the scope for the original connection is in the Public
Static GetConnection(). If you take away this function and use in-line code
does the same thing happen?

--
Brad

"Software is like melted pudding..."

I read in a KB article that if you use the using statement it will
invoke the dispose method of the Connection, it's the same thing as
using a try/finally block to ensure it gets done if any errors are thrown.

Have I misinterpreted this?

I don't see anywhere in your code where you are actually closing the
connection. Pooling is not going to work if you leave all connections
open.

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection
is sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[David Browne]

Could it be that the scope for the original connection is in the Public
Static GetConnection(). If you take away this function and use in-line
code
does the same thing happen?

No. That pattern is fine, I've used it many times.

David

 

[David Browne]

I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays open
(Process Info in Sql Server Enterprise Manager say the connection is
sleeping) and very soon I hit the currently defined 100 connections limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

This code looks fine. But you still probably have a connection leak
somewhere.

Here is a class I wrote to find where all the connections are

http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/msg/949045fa9619e950

David

 

[Kevin Yu [MSFT]]

Hi mc,

It seems that when SqlDataReaders are closed, connection objects still
remain to be open in your app. The connections are not returned to the
connection pool.

In this case, I suggest you try to use CommandBehavior when opening the
SqlDataReader. Use the following

SqlDataReader innnerRS =
innerCmd.ExecuteReader(CommandBehavior.CloseConnection);

instead of

SqlDataReader innnerRS = innerCmd.ExecuteReader();

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[mc]

Sorted now, I think!

found a couple of connection statements not converted to using blocks!!

what was odd was that they where being created and run for one
connection (the dodgy one) and then being reused by another (good)
connection. Leading me up the proverbial path!

only when I stepped through the whole page and scrutinised the activity
monitor did it become clear.

Thanks all for your efforts.

This means you have a connection leak.

Is this the only place in the entire application where you are using
connections?

I get the error "Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have occurred because all
pooled connections where in use and max pool size was reached"

When I look at "Current activity > Process Info" in "SQL Server Enterprise
Manager". I see over 100 connections (17 other background etc processes)
one for each time I ran the code below. The status of all is "sleeping"
and the command is "AWAITING COMMAND"

Connection pooling means that the pool manager keeps some 'open' if you
are looking in enterprise manager. Sounds odd that you are reaching 100.

However, are you getting error messages that no connections can be gotten
from the pool because they are all taken? If not, then it sounds like
they are being placed back in the pool properly. If the connections truly
remained open, after 100, you would no longer be able to open
connections, as it would say the pool is out of available ones.



I have a ASP.NET app which I have recently converted from OleDB to
SqlClient, however when I Get a connection, use and close it, It stays
open (Process Info in Sql Server Enterprise Manager say the connection is
sleeping) and very soon I hit the currently defined 100 connections
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd = new SqlCommand("StoredProcName",
innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
conn.Open();
return conn;
}

 

[David Browne]

Hi mc,

It seems that when SqlDataReaders are closed, connection objects still
remain to be open in your app. The connections are not returned to the
connection pool.

The code is fine. The "using" block closes the connection.

Here's a prettier print.

David

static void Main(string[] args)
{

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
using (SqlCommand innerCmd =
new SqlCommand("StoredProcName", innerConnect))
{
innerCmd.CommandType = CommandTyle.StoredProcedure;
innerCmd.Parameter.AddWithValue("@Parm1", Variable1);

SqlDataReader innnerRS = innerCmd.ExecuteReader();
if (innerRS.Read())
{
lblNumber1.Text =
innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
}
innerRS.Close();
}
}
}


public class DBHelper
{
public static SqlConnection GetConnection()
{
string connstr =
ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString;
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
return conn;
}
}
}

 

[Kevin Yu [MSFT]]

Hi David,

Sorry, I didn't see the code clearly. Because the following

using (SqlCommand innerConnect = DBHelper.GetConnection())

has to be

using (SqlConnection innerConnect = DBHelper.GetConnection())



This seem to be a connection leak. MC, if you branch the code to a smaller
sample, does this still reproduced?

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)