connections not being reused...

  • Thread starter Thread starter David Bartosik [MSFT MVP]
  • Start date Start date
D

David Bartosik [MSFT MVP]

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 Bartosik said:
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?
Click to expand...


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

http://groups.google.com/groups?selm=#[email protected]&output=gplain

David
 
Hi David,

Just a stupid idea: are you using the same connection string always?
 
Just a stupid idea: are you using the same connection string always?
--
Click to expand...

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.Message + "\n" +
"LineNumber: " + sqlex.Errors.LineNumber + "\n" +
"Source: " + sqlex.Errors.Source + "\n" +
"Procedure: " + sqlex.Errors.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();
}
}
}
}
 
You actually didn't show the connection string. Are you using Integrated
SQL Auth or standard SQL?

Jeff
David Bartosik said:
Just a stupid idea: are you using the same connection string always?

--
Click to expand...

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.Message + "\n" +
"LineNumber: " + sqlex.Errors.LineNumber + "\n" +
"Source: " + sqlex.Errors.Source + "\n" +
"Procedure: " + sqlex.Errors.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();
}
}
}
}
Click to expand...
 
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 Bartosik said:
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?
Click to expand...

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.Message + "\n" +
"LineNumber: " + sqlex.Errors.LineNumber + "\n" +
"Source: " + sqlex.Errors.Source + "\n" +
"Procedure: " + sqlex.Errors.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
 
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 said:
David Bartosik said:
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?
Click to expand...

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.Message + "\n" +
"LineNumber: " + sqlex.Errors.LineNumber + "\n" +
"Source: " + sqlex.Errors.Source + "\n" +
"Procedure: " + sqlex.Errors.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
Click to expand...
 
Back
Top