Connection problem

I have a multi-user web environment and amoccassionally receiving the following error msg with our SQL Server 2005 db:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I know there are numerous posts about this, but there doesn't seem to be a definitive "fix" for the issue after looking at the posts.

I am using the "WITH (NOLOCK)" option on some of the tables within my stored procedures. I need this option for certain tables I am accessing.

My ConnectionstringTimeout property is set for 45 seconds and my CommandTimeout property is set for 150 seconds.

Could a MS DBA please look into this issueASAP and get back with a definitive resolution?

btw, I am also using Connection Pooling with a min size of 2 and max size of 200.

Can someone please reply back who knows a definitive resolution to this problem?

|||

Hi wsyeager,

Well, seems thre is no ms dba answer your question here so i would try to deliver some effort. I searched through google and found this information. It makes sense to me

guess the problem is the connection pool. Recall that when you close
a connection from your .Net app, the connection to SQL Server is not
really closed. Instead ADO .Net lingers to it, for another 60 seconds,
and if the applicaiton opens a new connection during this time, it will
reuse the connection.

When you issue ALTER DATABASE WITH ROLLBACK IMMEDIATE, SQL Server will
terminate these connections. However, this is not something that ADO .Net
can detect. So it gives you a connection which it thinks is good, but
which isn't.

There are a couple of ways to approach this. One is to configure the
connection pool, so that all connections are dropped when you drop the
database. Another is to change the connection string, each time you
drop the database, as different connection strings gives different pools.

I would suggest that the best way, though, is simply to set up an exception
handler, so that you simply trap this error, and then try to reconnect.

See original artilce at:http://www.msdner.com/forum/thread476823.html

I sincerely hope it can help

|||

Bo, thanks a lot!

I simply look for that exception number (10054) in my middle tier object and retry the cmd again and it works fine now.