Re: Losing connections with SQL 2005
- From: "Jeremy" <jeremy-nospam@xxxxxxxxxxxxxx>
- Date: Sat, 24 Mar 2007 18:32:35 -0700
Bill, I do appreciate your detailed and thoughtful response. However, our
application did not experience dropped connections under sql 2k (or they
were so insignificant no one bothered me about it), whereas despite our best
efforts, I see many error reports every day with sql 2k5. Seems quite clear
that the problem is dramatically worse with the new version.
Jeremy
"William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxx> wrote in message
news:OaM5uelbHHA.4808@xxxxxxxxxxxxxxxxxxxxxxx
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: William \(Bill\) Vaughn
- Re: Losing connections with SQL 2005
- References:
- Re: Losing connections with SQL 2005
- From: Jeremy
- Re: Losing connections with SQL 2005
- From: William \(Bill\) Vaughn
- Re: Losing connections with SQL 2005
- Prev by Date: Re: Error: 18456, Severity: 14, State: 16
- Next by Date: Re: Losing connections with SQL 2005
- Previous by thread: Re: Losing connections with SQL 2005
- Next by thread: Re: Losing connections with SQL 2005
- Index(es):
Relevant Pages
|
