quote:

---

Originally posted by spirit1

it does clean up connections, but beacuse of the connection pooling you get sleeping spids.
if they keep piling up your iis will reset but that's probably a large number of spids that need to be left spleeping.
sql server supports a max of 32767 connections so that's not a problem.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

---

quote:

---

Originally posted by spirit1

define "persistant".

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

---

quote:

---

Originally posted by TG

Mladen, would you agree with the following statement?
I think the practice is to Close the connection as soon as you've used it, but the reference to the (closed) connection, as well as whatever connection properties you've set, persists for the scope of the variable.

Be One with the Optimizer
TG

---

quote:

---

Originally posted by spirit1

quote:

---

Originally posted by TG

Mladen, would you agree with the following statement?
I think the practice is to Close the connection as soon as you've used it, but the reference to the (closed) connection, as well as whatever connection properties you've set, persists for the scope of the variable.

Be One with the Optimizer
TG

---

scope of what variable?

when you close the connection in your code the connection state is changed to closed. because of the connection pooling the connection itself stays open for a few seconds then goes back to the pool. it's not really disposed and permanently destroyed. they do get destroeds after a while of unuse. i don't know how long "a while" is though.

---

quote:

---

[i]Originally posted by TGBrett, I have no idea of your .net experience. You may be way past my modest knowledge so please forgive me if that's true but would it help to see some .net code samples?

---

quote:

---

Sorry, I guess I need to dive into the pool

---

	/// <summary>
	/// SPCallerInstance represents a user instance.  It will loop through
	/// the sp call script, exec the current call after the specified delay.
	/// Log call statistics, track call count
	/// </summary>
	public class SPCallerInstance
	{
		SqlCommand _cmd;
		SqlDataReader _dr;
		SqlConnection _cn;

			try
			{
				_cn = new SqlConnection();
				_cn.ConnectionString = 
					"Application Name=" + TestController._appname +
					";Connection Timeout=35" +
					";Server=" + TestController._dbserver +
					";Database=" + TestController._db +
					";Encrypt=false" +
					";Integrated Security=" + TestController._integratedsecurity +
					";Persist Security Info=false" +
					";Connection Lifetime=0" +
					";Connection Reset=false" + 
					";Enlist=false" +
					";Max Pool Size=100" +
					";Pooling=true";

				if (TestController._integratedsecurity == "false")
				{
					_cn.ConnectionString = _cn.ConnectionString + 
						";User ID=" + TestController._dbaccountname +
						";Password=" + TestController._dbaccountpwd;
				}

				_cmd = new SqlCommand();
				_cmd.CommandTimeout = 30;
				_cmd.CommandType = System.Data.CommandType.Text;
				_cmd.Connection = _cn;
				_cmd.CommandText = TestController._alSPCalls[_cmdIndex].ToString();
	
				//------------------------------------------
				//exec the command and count rows and the call duration

				_cmd.Connection.Open();
				_dr = _cmd.ExecuteReader();

				if(_dr != null)
				{
					while(_dr.Read())
						_rowcount ++;
					_dr.Close();
				}
				if(_cmd.Connection.State != ConnectionState.Closed)
					_cmd.Connection.Close();
				//------------------------------------------
			}
			catch(Exception ex)
			{
				if(! _dr.IsClosed)
					_dr.Close();

				if(_cn.State != ConnectionState.Closed)
					_cn.Close();

				this.KillMe();

				LogError(ex);
			}
	}