Desperately need help finding connection leak

[Guest]

Hi, I have an ASP.NET website that crashes under heavy load.
I use a SQL Server DB. I get around 5500 hits per day. I keep getting the
timeout expieried connection pool error. Sometimes it even throws and error
about a DataReader connection being already open even though I only use Data
Sets in my code.
Please take a look and see if you can find my leak because I'm going nuts
here and am losing hope... I keep blaming the server and the server guys
blame my code.
All my methods look somewhat like these 2 right here:

System.Data.DataSet GetItems(int leagueID) {
System.Data.SqlClient.SqlConnection dbConnection =
(System.Data.SqlClient.SqlConnection)(Application["conn"]);

queryString="some select query";

System.Data.SqlClient.SqlCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_leagueID = new
System.Data.SqlClient.SqlParameter();
dbParam_leagueID.ParameterName = "@LeagueID";
dbParam_leagueID.Value = leagueID;
dbParam_leagueID.DbType = System.Data.DbType.Int32;
dbCommand.Parameters.Add(dbParam_leagueID);

System.Data.SqlClient.SqlDataAdapter dataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = dbCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();

try { dataAdapter.Fill(dataSet);}
finally { dbConnection.Close(); }

return dataSet;
}

int DDLMethod(int recordID) {
System.Data.SqlClient.SqlConnection dbConnection =
(System.Data.SqlClient.SqlConnection)(Application["conn"]);

string queryString = "some isert statment";
System.Data.SqlClient.SqlCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_recordID = new
System.Data.SqlClient.SqlParameter();
dbParam_recordID.ParameterName = "@RecordID";
dbParam_recordID.Value = recordID;
dbParam_recordID.DbType = System.Data.DbType.Int32;
dbCommand.Parameters.Add(dbParam_recordID);

int rowsAffected = 0;
dbConnection.Open();
try {
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally {
dbConnection.Close();
}

return rowsAffected;
}

Please list all the possible reason you can see for a connection leak... any
help would be GREATLY appreciated....

 

[Guadala Harry]

I had similar issues... someone in this group told me to DISPOSE the
connections in addition to closing. I added the following to my DAL
procedures and my connection pool issues went away.

cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Dispose();

Separately - in tracking down any offending procedures - you can use
Enterprise Manager to see what connections exist and what the last command
was on the connection. In Enterprise Manager, look under ServerName (node) |
Management (folder) | Current Activity (node) | Process Info (node). The
Process Info lists each connection to the server.... what user opened the
connection, the last command issued via that connection (double-click to see
this). You can even KILL the connection via this window - you'll have to
refresh the window explicitly in order to get up-to-date info. Separately
you can get other helpful info via Query Analyzer by executing sp_who and
the undocumented sp_who2 - these are useful for tracking down other
connection-specific info.

-GH

Hi, I have an ASP.NET website that crashes under heavy load.
I use a SQL Server DB. I get around 5500 hits per day. I keep getting the
timeout expieried connection pool error. Sometimes it even throws and error
about a DataReader connection being already open even though I only use Data
Sets in my code.
Please take a look and see if you can find my leak because I'm going nuts
here and am losing hope... I keep blaming the server and the server guys
blame my code.
All my methods look somewhat like these 2 right here:

System.Data.DataSet GetItems(int leagueID) {
System.Data.SqlClient.SqlConnection dbConnection =
(System.Data.SqlClient.SqlConnection)(Application["conn"]);

queryString="some select query";

System.Data.SqlClient.SqlCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_leagueID = new
System.Data.SqlClient.SqlParameter();
dbParam_leagueID.ParameterName = "@LeagueID";
dbParam_leagueID.Value = leagueID;
dbParam_leagueID.DbType = System.Data.DbType.Int32;
dbCommand.Parameters.Add(dbParam_leagueID);

System.Data.SqlClient.SqlDataAdapter dataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
dataAdapter.SelectCommand = dbCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();

try { dataAdapter.Fill(dataSet);}
finally { dbConnection.Close(); }

return dataSet;
}

int DDLMethod(int recordID) {
System.Data.SqlClient.SqlConnection dbConnection =
(System.Data.SqlClient.SqlConnection)(Application["conn"]);

string queryString = "some isert statment";
System.Data.SqlClient.SqlCommand dbCommand = new
System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;

System.Data.IDataParameter dbParam_recordID = new
System.Data.SqlClient.SqlParameter();
dbParam_recordID.ParameterName = "@RecordID";
dbParam_recordID.Value = recordID;
dbParam_recordID.DbType = System.Data.DbType.Int32;
dbCommand.Parameters.Add(dbParam_recordID);

int rowsAffected = 0;
dbConnection.Open();
try {
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally {
dbConnection.Close();
}

return rowsAffected;
}

Please list all the possible reason you can see for a connection leak... any
help would be GREATLY appreciated....

 

[David Browne]

Hi, I have an ASP.NET website that crashes under heavy load.
I use a SQL Server DB. I get around 5500 hits per day. I keep getting the
timeout expieried connection pool error. Sometimes it even throws and
error
about a DataReader connection being already open even though I only use
Data
Sets in my code.
Please take a look and see if you can find my leak because I'm going nuts
here and am losing hope... I keep blaming the server and the server guys
blame my code.
All my methods look somewhat like these 2 right here:

System.Data.DataSet GetItems(int leagueID) {
System.Data.SqlClient.SqlConnection dbConnection =
(System.Data.SqlClient.SqlConnection)(Application["conn"]);

There it is! This code is both wrong and not thread safe. You are
attempting to share a SqlConnection across threads.

You shouldn't be pulling a SqlConnection out of the Application object.
Instead store a connection string there and open a new connection each time:

Like this (notice how much less code it is too):

using System.Data;
using System.Data.SqlClient;

public static SqlConnection connect()
{
SqlConnection con = new SqlConnection(Application["connectionString"]);
con.Open();
return con;
}

DataSet GetItems(int leagueID)
{
using ( SqlConnection dbConnection = connect())
{
SqlCommand cmd = new SqlCommand("some select", dbConnection)
IDataParameter p_leagueID = new SqlParameter();
p_leagueID.ParameterName = "@LeagueID";
p_leagueID.Value = leagueID;
p_leagueID.DbType = DbType.Int32;
cmd.Parameters.Add(p_leagueID);

SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
}