Re: Losing connections with SQL 2005
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxx>
- Date: Sat, 24 Mar 2007 14:02:06 -0700
Ahem... the ability to stay connected once connected has been a perennial
issue. I can remember back to the earliest versions of SQL Server when
connections "aged" out for some unknown reason. No, I don't know the actual
reason, but symptoms lead me to believe that there is an ack/nak protocol in
the TDS that basically pings the application at the driver level to see if
the server or client is still there. This makes sense as SQL Server
automatically detects if the client falls into the sea or gets disconnected
for some reason. This polling has to be done on the server as it scans the
current connections to see if there is activity (work to be done) or if
there is still a client (albeit inactive) attached. I suspect that if the
client is busy (running Age of Empires or Windows OneCare) and can't respond
in time or the event is simply lost (as can happen in Windows
message-loop-based applications) the server thinks the client has dropped
off. My solution to this is to keep polling the server (once every 30
seconds or so seems to be more than enough) to keep the connection alive.
Does the Connection pooling mechanism do this on its own? BHOM, but I would
not be surprised. I have not seen evidence of this in the Profiler logs
though. I suspect it's done at the TDS provider level. Since we don't have
access to those APIs, one can constantly close and reopen the connection
(use the Connection pool) or poll (which is pretty easy and faster as it
does not require reauthenication, resetting the connection and loss of the
server state). To poll you can execute any SQL like "USE <initial catalog>"
or "DECLARE i as INT".
I'm not sure I said that keeping a connection open is a "preferred" method,
just a perfectly viable method--assuming you know the costs.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"Jeremy" <jeremy-nospam@xxxxxxxxxxxxxx> wrote in message
news:uAzf37ibHHA.2088@xxxxxxxxxxxxxxxxxxxxxxx
Darrel, we have exactly this issue, but with a different front end
language. We converted in Nov 06 and immediately our remote clients began
having serious issues just as you describe. We reduced that somewhat by
implementing an ado connection object that "pings" periodically to keep
the connection alive, but in recent weeks the problem has gotten worse
again.
I ran into Bill Vaughn at a user group meeting late last year and picked
his brain (or should I say "opened the tap"). The bottom line seemed to
be: the most effective solution is to upgrade the app to dotnet; changing
to sql native client can help somewhat; "pinging" can also help.
Upgrading our app is a really big task, and we haven't done it. We did
the ping thing and the native client thing, but as I said, those things
alone are not the solution. We opened a ticket with MS and spend hours in
emails and on the phone with overseas MS support people on this issue.
They really didn't have a clue.
By the way, look at Bill's posting on 3/2/07 in which he says
unequivocably that for a win app, connecting and staying connected is a
preferred practice. Obviously Sue Hoegemeier does not agree.
My view is that MS is either unaware of, in denial about, or unwilling to
acknowledge and correct this issue.
Jeremy
"Darrel Miller" <DarrelMiller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46643664-0C8E-4C94-8DE4-4704518EAE8A@xxxxxxxxxxxxxxxx
We have a VB6 application that I recently converted over from SQL Server
2000
to SQL Server 2005. At numerous customer locations I am now experiencing
occasional "lost connection" problems. This never happened with SQL
2000.
Our client app does not use pooled connections and I have not been able
to
find a clean way to recover from the broken connection problem. If I
check
the status of the Connection it claims that it is open, it is only if I
try
to make a database request that I get the error back. Re-opening the
connection solves the problem.
Is anyone aware of SQL Server 2005 doing anything different with respect
to
keeping database connections open?
Thanks,
.
- Follow-Ups:
- Re: Losing connections with SQL 2005
- From: Jeremy
- Re: Losing connections with SQL 2005
- References:
- Re: Losing connections with SQL 2005
- From: Jeremy
- Re: Losing connections with SQL 2005
- Prev by Date: Re: difference between Owned schemas and Roles members
- Next by Date: Re: Error: 18456, Severity: 14, State: 16
- Previous by thread: Re: Losing connections with SQL 2005
- Next by thread: Re: Losing connections with SQL 2005
- Index(es):
Relevant Pages
|
