connections not being reused...

I have a Windows Service written in C# .net 1.1 that writes to SQL Server
2000. It has been deployed for some time now with no issues.
now recent performance issues with the SQL Server have pointed to an
apparent issue with the Windows Service. The service opens and closes all
it's connection objects and has try/catch/finally blocks in place. (again
this "had" been working properly). Yet now SQL Server is logging several
connections from the service that continue to grow in number. And it appears
that connections are not being reused from the connection pool.
What can I look at as to why connections in the pool would not be reused
versus new connections being created? And was there any recent patch that
may have effected this?

David Bartosik - [MSFT MVP]
www.publishermvps.com
www.davidbartosik.com

David
Wed Nov 10 16:42:02 CST 2004


"David Bartosik [MSFT MVP]" <dbartosik@community.nospam> wrote in message
news:uSdKuH3xEHA.1404@TK2MSFTNGP11.phx.gbl...
>I have a Windows Service written in C# .net 1.1 that writes to SQL Server
>2000. It has been deployed for some time now with no issues.
> now recent performance issues with the SQL Server have pointed to an
> apparent issue with the Windows Service. The service opens and closes all
> it's connection objects and has try/catch/finally blocks in place. (again
> this "had" been working properly). Yet now SQL Server is logging several
> connections from the service that continue to grow in number. And it
> appears that connections are not being reused from the connection pool.
> What can I look at as to why connections in the pool would not be reused
> versus new connections being created? And was there any recent patch that
> may have effected this?


Here's an idea for tracing when a SqlConnection hits the Garbage Collector
without having been properly closed.

http://groups.google.com/groups?selm=%23TqDW17RDHA.1624%40tk2msftngp13.phx.gbl&output=gplain

David

Miha
Thu Nov 11 02:53:10 CST 2004

Hi David,

Just a stupid idea: are you using the same connection string always?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

"David Bartosik [MSFT MVP]" <dbartosik@community.nospam> wrote in message
news:uSdKuH3xEHA.1404@TK2MSFTNGP11.phx.gbl...
>I have a Windows Service written in C# .net 1.1 that writes to SQL Server
>2000. It has been deployed for some time now with no issues.
> now recent performance issues with the SQL Server have pointed to an
> apparent issue with the Windows Service. The service opens and closes all
> it's connection objects and has try/catch/finally blocks in place. (again
> this "had" been working properly). Yet now SQL Server is logging several
> connections from the service that continue to grow in number. And it
> appears that connections are not being reused from the connection pool.
> What can I look at as to why connections in the pool would not be reused
> versus new connections being created? And was there any recent patch that
> may have effected this?
>
> David Bartosik - [MSFT MVP]
> www.publishermvps.com
> www.davidbartosik.com
>
>

David
Thu Nov 11 08:26:15 CST 2004

> Just a stupid idea: are you using the same connection string always?
>
> --

the connection string is static in the xml config file.
I'm throwing the code in here for review (below).
Got new info this morning from the DBA's, I'm now being told that my service
is deployed on multiple servers (I'd thought it was only the one) and that
this connection pool issue is only on one of their servers, the remaining
servers are running the service with a pool of 5 connections. But on the
problem server they can watch as the connection pool grows increasingly. The
connection pool is managed by the Framework, is it something with the
Framework on this one server?


public void DoWork()
{
string sp = "";
string errorMessages = "";
SqlConnection con = null;
SqlCommand cmd;

if (Thread.CurrentThread.ThreadState ==
System.Threading.ThreadState.Running)//checks for an abort request
{
try
{
sp = "exec " + procedureName + " " + parameterList;
con = new SqlConnection(dbConnection);
cmd = new SqlCommand(sp, con);
cmd.CommandTimeout = cmdTimeout;
con.Open();
cmd.ExecuteNonQuery();
}

catch(SqlException sqlex)//server side exception handling
{
for (int i=0; i < sqlex.Errors.Count; i++)//grab errors
{
errorMessages += "Index #" + i + "\n" +
"Message: " + sqlex.Errors[i].Message + "\n" +
"LineNumber: " + sqlex.Errors[i].LineNumber + "\n" +
"Source: " + sqlex.Errors[i].Source + "\n" +
"Procedure: " + sqlex.Errors[i].Procedure + "\n";
}

try //log exception to exception database
{
sp = "exec " + "RaiseResponseException " + serverName + ", " +
"@errMsg";
con = new SqlConnection(dbConnection);
cmd = new SqlCommand(sp, con);

cmd.Parameters.Add(new SqlParameter("@errMsg", SqlDbType.VarChar));
cmd.Parameters["@errMsg"].Value=errorMessages;

cmd.CommandTimeout = cmdTimeout;
con.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex) //if log to db fails log to host
{
EventLog.WriteEntry(String.Format("ServerAuditService"),
String.Format("The Exception database failed to log SQL Exception: ") +
errorMessages + ex.ToString() + serverName + procedureName + parameterList,
EventLogEntryType.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
}

catch(Exception ex)//client side exception handling
{
try //log exception to exception database
{
sp = "exec " + "RaiseResponseException " + serverName + ", " +
ex.ToString() + procedureName + parameterList;
con = new SqlConnection(dbConnection);
cmd = new SqlCommand(sp, con);
cmd.CommandTimeout = cmdTimeout;
con.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex1) //if log to db fails log to host
{
EventLog.WriteEntry(String.Format("ServerAuditService"),
String.Format("The Exception database failed to log Exception: ") +
ex.ToString() + ex1.ToString() + serverName + procedureName + parameterList,
EventLogEntryType.Error);
}
finally
{
if (con != null)
{
con.Close();
}
}
}
finally
{
if (con != null)
{
con.Close();
}
}
}
}

Jeff
Thu Nov 11 10:53:13 CST 2004

You actually didn't show the connection string. Are you using Integrated
SQL Auth or standard SQL?

Jeff
"David Bartosik [MSFT MVP]" <dbartosik@community.nospam> wrote in message
news:OYWIop$xEHA.1196@TK2MSFTNGP15.phx.gbl...
> > Just a stupid idea: are you using the same connection string always?
> >
> > --
>
> the connection string is static in the xml config file.
> I'm throwing the code in here for review (below).
> Got new info this morning from the DBA's, I'm now being told that my
service
> is deployed on multiple servers (I'd thought it was only the one) and that
> this connection pool issue is only on one of their servers, the remaining
> servers are running the service with a pool of 5 connections. But on the
> problem server they can watch as the connection pool grows increasingly.
The
> connection pool is managed by the Framework, is it something with the
> Framework on this one server?
>
>
> public void DoWork()
> {
> string sp = "";
> string errorMessages = "";
> SqlConnection con = null;
> SqlCommand cmd;
>
> if (Thread.CurrentThread.ThreadState ==
> System.Threading.ThreadState.Running)//checks for an abort request
> {
> try
> {
> sp = "exec " + procedureName + " " + parameterList;
> con = new SqlConnection(dbConnection);
> cmd = new SqlCommand(sp, con);
> cmd.CommandTimeout = cmdTimeout;
> con.Open();
> cmd.ExecuteNonQuery();
> }
>
> catch(SqlException sqlex)//server side exception handling
> {
> for (int i=0; i < sqlex.Errors.Count; i++)//grab errors
> {
> errorMessages += "Index #" + i + "\n" +
> "Message: " + sqlex.Errors[i].Message + "\n" +
> "LineNumber: " + sqlex.Errors[i].LineNumber + "\n" +
> "Source: " + sqlex.Errors[i].Source + "\n" +
> "Procedure: " + sqlex.Errors[i].Procedure + "\n";
> }
>
> try //log exception to exception database
> {
> sp = "exec " + "RaiseResponseException " + serverName + ", " +
> "@errMsg";
> con = new SqlConnection(dbConnection);
> cmd = new SqlCommand(sp, con);
>
> cmd.Parameters.Add(new SqlParameter("@errMsg", SqlDbType.VarChar));
> cmd.Parameters["@errMsg"].Value=errorMessages;
>
> cmd.CommandTimeout = cmdTimeout;
> con.Open();
> cmd.ExecuteNonQuery();
> }
> catch(Exception ex) //if log to db fails log to host
> {
> EventLog.WriteEntry(String.Format("ServerAuditService"),
> String.Format("The Exception database failed to log SQL Exception: ") +
> errorMessages + ex.ToString() + serverName + procedureName +
parameterList,
> EventLogEntryType.Error);
> }
> finally
> {
> if (con != null)
> {
> con.Close();
> }
> }
> }
>
> catch(Exception ex)//client side exception handling
> {
> try //log exception to exception database
> {
> sp = "exec " + "RaiseResponseException " + serverName + ", " +
> ex.ToString() + procedureName + parameterList;
> con = new SqlConnection(dbConnection);
> cmd = new SqlCommand(sp, con);
> cmd.CommandTimeout = cmdTimeout;
> con.Open();
> cmd.ExecuteNonQuery();
> }
> catch(Exception ex1) //if log to db fails log to host
> {
> EventLog.WriteEntry(String.Format("ServerAuditService"),
> String.Format("The Exception database failed to log Exception: ") +
> ex.ToString() + ex1.ToString() + serverName + procedureName +
parameterList,
> EventLogEntryType.Error);
> }
> finally
> {
> if (con != null)
> {
> con.Close();
> }
> }
> }
> finally
> {
> if (con != null)
> {
> con.Close();
> }
> }
> }
> }
>
>

David
Thu Nov 11 10:56:49 CST 2004

standard...


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="threadLimit" value="5" /><!-- number of worker processes-->
<add key="dbConnection"
value="server=dev;uid=mtr;pwd=pswd;database=monitor; connect timeout=30"
/><!-- connection string -->
<add key="cmdTimeout" value="240" /><!-- in seconds -->
<add key="managerSleep" value="30000" /><!-- in milliseconds -->
<add key="workerSleep" value="3000" /><!-- in milliseconds -->
<add key="threadTimeout" value="4800000000"/><!-- in 100 nanoseconds -->
</appSettings>
</configuration>

David
Thu Nov 11 16:38:09 CST 2004


"David Bartosik [MSFT MVP]" <dbartosik@community.nospam> wrote in message
news:OYWIop$xEHA.1196@TK2MSFTNGP15.phx.gbl...
>> Just a stupid idea: are you using the same connection string always?
>>
>> --
>
> the connection string is static in the xml config file.
> I'm throwing the code in here for review (below).
> Got new info this morning from the DBA's, I'm now being told that my
> service is deployed on multiple servers (I'd thought it was only the one)
> and that this connection pool issue is only on one of their servers, the
> remaining servers are running the service with a pool of 5 connections.
> But on the problem server they can watch as the connection pool grows
> increasingly. The connection pool is managed by the Framework, is it
> something with the Framework on this one server?
>
>

Unfortunately that code is full of connection leaks. If a Sql Exception is
thrown you are setting
con = new SqlConnection
which lets the _old_ SqlConnection drift off into the unreachable spaces of
the managed heap without being closed.

Here's a rewrite using a "using" block for the SqlConnection, it prevents
you from having to have all the finally blocks, so it makes the code shorter
too.


public static SqlConnection Connect()
{
SqlConnection con = new SqlConnection(dbConnection);
con.Open();
return con;
}


public void DoWork()
{
string sp = "";
string errorMessages = "";

if (Thread.CurrentThread.ThreadState ==
System.Threading.ThreadState.Running)//checks for an abort request
{
using(SqlConnection con = Connect())
{
try
{
sp = "exec " + procedureName + " " + parameterList;
SqlCommand cmd = new SqlCommand(sp, con);
cmd.CommandTimeout = cmdTimeout;
cmd.ExecuteNonQuery();
}
catch(SqlException sqlex)//server side exception handling
{
for (int i=0; i < sqlex.Errors.Count; i++)//grab errors
{
errorMessages += "Index #" + i + "\n" +
"Message: " + sqlex.Errors[i].Message + "\n" +
"LineNumber: " + sqlex.Errors[i].LineNumber + "\n" +
"Source: " + sqlex.Errors[i].Source + "\n" +
"Procedure: " + sqlex.Errors[i].Procedure + "\n";
}

try //log exception to exception database
{
sp = "exec " + "RaiseResponseException " + serverName + ", " +
"@errMsg";
//con = new SqlConnection(dbConnection);
SqlCommand cmd = new SqlCommand(sp, con);

cmd.Parameters.Add(new SqlParameter("@errMsg",
SqlDbType.VarChar));
cmd.Parameters["@errMsg"].Value=errorMessages;

cmd.CommandTimeout = cmdTimeout;
//con.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex) //if log to db fails log to host
{
EventLog.WriteEntry(String.Format("ServerAuditService"),
String.Format("The Exception database failed to log SQL
Exception: ") +
errorMessages + ex.ToString() + serverName + procedureName +
parameterList,
EventLogEntryType.Error);
}
}
catch(Exception ex)//client side exception handling
{
try //log exception to exception database
{
sp = "exec " + "RaiseResponseException " + serverName + ", " +
ex.ToString() + procedureName + parameterList;
SqlCommand cmd = new SqlCommand(sp, con);
cmd.CommandTimeout = cmdTimeout;
cmd.ExecuteNonQuery();
}
catch(Exception ex1) //if log to db fails log to host
{
EventLog.WriteEntry(String.Format("ServerAuditService"),
String.Format("The Exception database failed to log Exception:
") +
ex.ToString() + ex1.ToString() + serverName + procedureName +
parameterList,
EventLogEntryType.Error);
}

}
}

}
}


David

David
Fri Nov 12 09:32:37 CST 2004

thanks for the advice. I'll implement this right now and see what happens.

I did recently learn about the "using" and did just use it in my most recent
service app, but hadn't considered a rewrite of my previous service app's. I
will do so now though.

David Bartosik - [MSFT MVP]
www.publishermvps.com
www.davidbartosik.com

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:ufHYf8DyEHA.804@TK2MSFTNGP12.phx.gbl...
>
> "David Bartosik [MSFT MVP]" <dbartosik@community.nospam> wrote in message
> news:OYWIop$xEHA.1196@TK2MSFTNGP15.phx.gbl...
>>> Just a stupid idea: are you using the same connection string always?
>>>
>>> --
>>
>> the connection string is static in the xml config file.
>> I'm throwing the code in here for review (below).
>> Got new info this morning from the DBA's, I'm now being told that my
>> service is deployed on multiple servers (I'd thought it was only the one)
>> and that this connection pool issue is only on one of their servers, the
>> remaining servers are running the service with a pool of 5 connections.
>> But on the problem server they can watch as the connection pool grows
>> increasingly. The connection pool is managed by the Framework, is it
>> something with the Framework on this one server?
>>
>>
>
> Unfortunately that code is full of connection leaks. If a Sql Exception
> is thrown you are setting
> con = new SqlConnection
> which lets the _old_ SqlConnection drift off into the unreachable spaces
> of the managed heap without being closed.
>
> Here's a rewrite using a "using" block for the SqlConnection, it prevents
> you from having to have all the finally blocks, so it makes the code
> shorter too.
>
>
> public static SqlConnection Connect()
> {
> SqlConnection con = new SqlConnection(dbConnection);
> con.Open();
> return con;
> }
>
>
> public void DoWork()
> {
> string sp = "";
> string errorMessages = "";
>
> if (Thread.CurrentThread.ThreadState ==
> System.Threading.ThreadState.Running)//checks for an abort request
> {
> using(SqlConnection con = Connect())
> {
> try
> {
> sp = "exec " + procedureName + " " + parameterList;
> SqlCommand cmd = new SqlCommand(sp, con);
> cmd.CommandTimeout = cmdTimeout;
> cmd.ExecuteNonQuery();
> }
> catch(SqlException sqlex)//server side exception handling
> {
> for (int i=0; i < sqlex.Errors.Count; i++)//grab errors
> {
> errorMessages += "Index #" + i + "\n" +
> "Message: " + sqlex.Errors[i].Message + "\n" +
> "LineNumber: " + sqlex.Errors[i].LineNumber + "\n" +
> "Source: " + sqlex.Errors[i].Source + "\n" +
> "Procedure: " + sqlex.Errors[i].Procedure + "\n";
> }
>
> try //log exception to exception database
> {
> sp = "exec " + "RaiseResponseException " + serverName + ", " +
> "@errMsg";
> //con = new SqlConnection(dbConnection);
> SqlCommand cmd = new SqlCommand(sp, con);
>
> cmd.Parameters.Add(new SqlParameter("@errMsg",
> SqlDbType.VarChar));
> cmd.Parameters["@errMsg"].Value=errorMessages;
>
> cmd.CommandTimeout = cmdTimeout;
> //con.Open();
> cmd.ExecuteNonQuery();
> }
> catch(Exception ex) //if log to db fails log to host
> {
> EventLog.WriteEntry(String.Format("ServerAuditService"),
> String.Format("The Exception database failed to log SQL
> Exception: ") +
> errorMessages + ex.ToString() + serverName + procedureName +
> parameterList,
> EventLogEntryType.Error);
> }
> }
> catch(Exception ex)//client side exception handling
> {
> try //log exception to exception database
> {
> sp = "exec " + "RaiseResponseException " + serverName + ", " +
> ex.ToString() + procedureName + parameterList;
> SqlCommand cmd = new SqlCommand(sp, con);
> cmd.CommandTimeout = cmdTimeout;
> cmd.ExecuteNonQuery();
> }
> catch(Exception ex1) //if log to db fails log to host
> {
> EventLog.WriteEntry(String.Format("ServerAuditService"),
> String.Format("The Exception database failed to log
> Exception: ") +
> ex.ToString() + ex1.ToString() + serverName + procedureName +
> parameterList,
> EventLogEntryType.Error);
> }
>
> }
> }
>
> }
> }
>
>
> David
>