Re: sleeping processes with awaiting command status

[Michael Ramey]

When you are completely done with your connection object "conn", do you
..dispose() it? With this new ado.net way of connection pooling,
conn.close() can be a little misleading as it just returns it to the
connection pool. You can wait for .NET's Garbage truck to come around, but
if the application is as data intensive as you say it is, it's can't hurt to
tip the driver so he takes your trash out now.

--Michael

I am having a problem with a web application which is locking up the SQL

2000 server. Connections arent being killed from the web server to the SQL
server. This is a very data intensive application so under heavy traffic it
can happen very quickly. Were talking hundreds of processes in the sleeping
state with status "Awaiting Command" The only solution to the problem is
rebooting either the SQL server or the web server.

The application has multiple layers, with stored procedures, a C# class

library dll and a ASP.NET in C#. I've isolated the problem to at least one
part of the code. Starting from the ASP.NET section i will walk through the
levels down to the stored procedures.

the following is the OnInit function of the aspx.cs file, ive added to it

a few initialization steps for the page

override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//

if(Session["LoggedInStudentID"] == null)
{
Response.Redirect(ROOT + "default.asp");
}

LoggedInStudent = new Student(Session["LoggedInStudentID"].ToString());
this.ROOT = System.Configuration.ConfigurationSettings.AppSettings.Get("CurrentDomain");
this.TERMID =

System.Configuration.ConfigurationSettings.AppSettings.Get("ContractRenewal
- TermID");

InitializeComponent();
base.OnInit(e);
}

LoggedInStudent is a private member of the page and is of type student which is defined in a dll
it is in the constructor of this class where it tends to freeze up among other places
Here is the constructor of the Student class

public Student(string NationalID)
{
SqlConnection conn = new

SqlConnection(System.Configuration.ConfigurationSettings.AppSettings.Get("Co
ntractRenewal - Connection String"));

conn.Open();

SqlCommand comm = new SqlCommand("spStudent_GetRMSDetails", conn);
comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("@national_id", SqlDbType.VarChar, 15);
comm.Parameters["@national_id"].Value = NationalID;

SqlDataAdapter dad = new SqlDataAdapter(comm);
DataTable dtbl = new DataTable();

dad.Fill(dtbl);
dad.Dispose();

if(dtbl.Rows.Count > 0)
{
DataRow dr = dtbl.Rows[0];
first_name = dr["ix_First_Name"].ToString();
last_name = dr["ix_Last_Name"].ToString();
gender = dr["Sex"].ToString();
rms_id = Convert.ToInt32(dr["pk_RMS_ID"]);
national_id = dr["ix_National_ID"].ToString();


if(conn.State != ConnectionState.Open)
conn.Open();

SqlCommand comm_req = new

SqlCommand("spStudent_GetRequestedRoommates", conn);

comm_req.CommandType = CommandType.StoredProcedure;

comm_req.Parameters.Add("@rms_person_id_requested", SqlDbType.Int);
comm_req.Parameters["@rms_person_id_requested"].Value = rms_id;

SqlDataAdapter dad_req = new SqlDataAdapter(comm_req);
DataTable dtbl_req = new DataTable();

dad_req.Fill(dtbl_req);


//if they exist in the Requested roomates table as a requested roommate set the value

if(dtbl_req.Rows.Count > 0)
{
DataRow dr_req = dtbl_req.Rows[0];
is_requested_roommate = true;
requested_by = new Student(Convert.ToInt32(dr_req["RMSPersonID"]));
}

dad_req.Dispose();
dtbl_req.Dispose();

SqlCommand comm_credit = new SqlCommand("spStudent_GetCreditHours", conn);
comm_credit.CommandType = CommandType.StoredProcedure;

comm_credit.Parameters.Add("@national_id", SqlDbType.VarChar, 10);
comm_credit.Parameters["@national_id"].Value = national_id;

SqlDataAdapter dad_credit = new SqlDataAdapter(comm_credit);
DataTable dtbl_credit = new DataTable();

dad_credit.Fill(dtbl_credit);


if(dtbl_credit.Rows.Count > 0)
{
DataRow dr_credit = dtbl_credit.Rows[0];
accum_credit_hours = Convert.ToInt32(dr_credit["AccumulatedHours"]);
scheduled_credit_hours = Convert.ToInt32(dr_credit["CurrentHours"]);
}

dad_credit.Dispose();
dtbl_credit.Dispose();
base.MakeConnected();
}

conn.Close();
}


not sure if theres a need for listing the stored procedurs, there just

basic select * from table where Fields = @parameters etc. etc.

This is a serious problem i am a developer for a state school and this

application is for students choosing their housing assignments.