I originally encountered this issue with a client in 2006.

If you use the Close and Dispose methods of a Connection object in ADO.NET is the connection immediately returned to the pool? You might think so, but not really.

After doing some research I found out that closing the connection .NET doesn't actually close it. Once a connection is closed in .NET, the connection is marked as dead but remains open. The CLR then cleans up, finally closes, these connections every 4-8 minutes. All connections will close if the app domain that launched the app shuts down. Per Microsoft, this is by design.

I tested this out to verify by running a test app that executes a SqlDataReader. After the SqlDataReader is executed and the Close method is called I ran sp_who. Sure enough the connection was still open. I continued to run sp_who every 30 seconds. After 6 minutes, the connection finally closed.

What to do, what to do. After doing a little more digging I found a method new to .NET 2.0. Enter SqlConnection.ClearPool(SqlConnection connection). By calling this method all inactive connections in the pool are cleaned up (closed) immediately.

Not trusting MSFT I decided to try this out. I modified my test app to have a max pool size of 10, launch 10 threads with each thread executing a SqlDataReader 1000 times. The first time I ran it without calling ClearPool. I then monitored the connections via sp_who. Sure enough 10 connections showed up. After the SqlDateaders finished executing the connections remained opened for 7 minutes.

I then ran the app again utilizing the ClearPool method after each SqlDateader finished executing. This time as when I executed sp_who I saw the number of connections increase and decrease depending on the number of active SqlDataReaders. Once all readers were done executing no connections remained open.

What does all this mean? I'm not quite sure, but I will say that it is important to consider how you handle your connections with .NET, especially in a high transactional environment.

As an addendum, when running on Vista this issue never occurs. It looks MSFT has updated garbage collection to prevent this from happening.

ag

Let me preface this by saying I hope you never run into this problem.

So back to the question "When is zero not a number?" Simple when you try to convert a string of zero (i.e. "0") to the number zero (i.e. 0) and you have a corrupt registry entry.

Allow me to explain. At a former client a development team was beginning to roll their .NET 2.0 code into test when the following error was encountered on an intermittant basis:  "Input string was not in a correct format".

After a bit of debugging on the client machine I found the error was encountered when code similar to the following was executed:

string myString = "0";
int myInt = int.Parse(myString);

(I could go into why this code was in place, but that's a topic for another entry.)

It was obvious that something on the machine wasn't quite right as this conversion should happen without problem. However, locating the problem wasn't easy due to two factors:
1. Another user could log on to the same machine and run the above code without problem.
2. The application was being rolled to an initial group of 10 machines, but the problem didn't occur on every machine.

The bigger issue faced by the team was that this application was going to be deployed to thousands of client machines. With a problem like this in place, the potential deployment of the application was at risk.

After hitting my head against the all for a week or so I ran across the following article: http://support.microsoft.com/kb/919236. While the issue was with the SQL Server 2005 Management Studio, the symptom sound similar. According to the article the cause of the problem has to do with the HKEY_CURRENT_USER\Control Panel\International\sPositiveSign being corrupt. I took a look at the key on the machine and the value looked OK. However, I removed the key, recreated it, and the problem was resolved.

Unfortunately, I was never able to determine what corrupted the key. My suspicion was that an update pushed by the client's IT group corrupted the key. However, I was not able verify this. So, if you know of an app or update out there that corrupts this key let me know (it'll help me sleep better at night).

Like I said...simple.

ag