Connection timeout - Sleeping Processes - Connection Pools? Why oh Why

[mark.ellul]

Hi Everyone,

I have a problem with a system in production. Basically we are getting
timeouts on getting a connection from the connection pool... I will
give the background now...

We have a Web Server and a Database Server with the below
configurations.

Web Server
Win2k3
IIS6

Database
Win2k3
Sql Server 2000 (8.00.2039 - SP4)

We use MSMQ on the Web server to do message processing...

So we have one HTTP Application sending messages to another HTTP
Application. Lets call the first one MessageSender HTTP Application and
the other the MessageProcessor HTTP Application.

So we have asynchronous Queue Listeners in the MessageProcessor
Application, and to make sure we never get overwhelm with messages I
have implemented a thread counting approach to make sure we never have
too many threads running at one... At the moment I have set the maximum
to 5. Each thread calls a stored procedure on the Database...

Anyway for some freak reason... the queue listener stopped listening...
and we had a backlog of 8000 messages on one queue... When we restarted
the MessageProcessor the queue listener was throttles so no more than 5
threads could run and after about 300 or so messages basically it took
up the whole connection pool and then started throwing exceptions...

I managed to stop the QueueListener before it brought down the whole
application... And I had these observations ... There were similar
amounts of "sleeping" processes which only ever disappeared when the
Application Pool in IIS was recycled or after a considerable amount of
time (I am assuming Garbage Collection Kicked in).

So the simple answer is that we are not closing the connections... well
we went through the whole application and all of the connections are
closed... we tried a close and a dispose call on the
connections...Which started throwing the below error (even though we do
not reuse connection objects in each thread).

ExecuteReader requires an open and available Connection. The
connection's current state is Closed

So we do not know where to look to next...

What could be causing the connections to be held and not reused in the
connection pool, is it linked with the sleeping processes?

Any Help would be muchly appreciated...

Regards

Mark

 

[mark.ellul]

Has anyone run into this before?

Please we are stuck, we have tried disposing of commands and
sqlconnections and nada!

Regards

Mark

 

[Guest]

I had a similar error.
My code had explicitly closed all of the connections, but I was still maxing
out the connections from the pool.

Finally, I disposed of every connection after use and the problem went away.
However, this seems extreme to me.

Anyway, the error you're getting implies a bug in your code.
Are you using Visual Studio and stepping through the code?

 

[mark.ellul]

Hi Adam,

Thanks for your response...

Yes we are using Visual Studio .NET 2003, the problem is that we have
only seen the error in production... Especially if we had 1000s of
messages to process...

I will try and dispose of all of the connections... and set up a
similar environment on a machine were we can debug with visual studio
..net

Regards

Mark

 

[Guest]

This might be a longshot, but we had another strange error about a year ago.

We put a web app on our production server in test mode (pointing to our test
database). Then, for our go-live, we replaced it with the production version.

For some reason, about 20% of the users were logging into the old version
AFTER the update! (It was easy to tell since they were in a completely
separate database).

We did two things:

1. First delete the files from the Windows folder (e.g.,
C:\Windows\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
Files\[AppName]\...). The dot net server seems to cache old code.

2. Made sure we updated ALL of the files when we made an upgrade (not just
the dll's in the bin folder).

This seemed to fix it.

Perhaps your latest code is good but your server is running old code?
I know, it's a longshot.

Also, make sure you use Try/Catch/Finally in the code where you're making
SQL connections and close/dispose the connection in the Finally step. This
will make sure the connection gets closed even if there's a runtime error.