Okay guys, this is more of a comment in case anyone else has the same issue
and decides to do a search for the problem.

Short history:
We have build a system that performs quite a lot of database calls (on
multiple threads) both to retrieve and write data. Our application's data
quickly grew then exceptions started being thrown in our data access code,
SQL Server was terminating different processes with -> "Transaction was
deadlocked on lock resources with another process and has been chosen as the
deadlock victim". So I went attempting to investigate the connection pool of
ADO.NET with some test applications.
Wrote a simple app to queue up sereral calls to the DB on the thread
pool to do some simple work.

Issue:
After the specified "max connections" for connections in the connection
pool were reached the application kept locking up. Over and over again ...
try as I might. I said to myself ... self ! why isn't this working!? ... the
connections should be going back to the pool when the db job was done ... I
saw the connection being closed. I even "Dispose"-ed of the connection and
saw the disposed event handler get called.

The problem:
It "seemes" that the issues was setting SQL Server debugging in
VisualStudio.NET project options to TRUE. When this option is turned on it
seems that connections arn't really returned to the connection pool when
they are closed by the application.

Has anyone else out there had this issue? Am I correct in asuming the above?

FYI: The "Transaction deadlock..." issues were solved using SQL locking
hints and paying closer attention to Transaction isolation levels and locks
during operations. There is tonnes of posts in this news group about that
already.

Cordell Lawrence [clawrence (AT) teleios-systems (DOT) com]
Teleios Systems Ltd. [www.teleios-systems.com]

Sure thing Mohammed,
Here's the code. This code does not contain the distruction of the
connection though, but feel free to wrap it in a Using(..) { } statement if
you like.

public class ConsoleApplication
{
private string connectionString = "Address=.; Database=Northwind;
Trusted_Connection=true; Max Pool Size=30; Min Pool Size=1; Pooling=True";
private int count;

[STAThread]
public static void Main(string[] args)
{
try
{
ConsoleApplication ca = new ConsoleApplication();
ca.Begin(100);
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
Console.WriteLine("App Execution Complete.");
Console.Read();
}
}

public void Begin(int frequency)
{
for(int i = 0; i < frequency; i++)
{
ThreadPool.QueueUserWorkItem(new WaitCallback(this.Work));
// Simulate rest : you can raise this if you like
Thread.Sleep(100);
}
}

public void Work(object param)
{
SqlConnection connection = new SqlConnection(this.connectionString);

SqlCommand command = new SqlCommand("Select * from Customers",
connection);
command.CommandType = CommandType.Text;

DataSet dsData = new DataSet();
new SqlDataAdapter(command).Fill(dsData);

Console.WriteLine("{0} - DB Job complete", Interlocked.Increment(ref
this.count));
}

}

"Mohamoss" <mohamed.mossad (AT) egdsc (DOT) microsoft.com> wrote