Re: Losing connections with SQL 2005

Ok, there are a lot of "fixes" in SS2K5 that might have had this unintended
side-effect. Have you installed the latest SP2? I think there is a SP2a (or
b).
I would log a bug with MS. The (more or less) monitor this group (but pay
more attention to the MSDN forums).


--
____________________________________
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:Oa3%2321nbHHA.2300@xxxxxxxxxxxxxxxxxxxxxxx
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,









.

Relevant Pages

  • RE: Error 17835 - When encryption and Shared Memory are enabled
    ... Did you encounter this error when you connected to your SQL Server ... Did your SQL Server instance use a certificate explicitly specified by ... you configure your client connection settings "Force Protocol Encryption" ...
    (microsoft.public.sqlserver.connect)
  • Re: Connection Error: ADO.Net and SQL Server 2005
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... I can connect to the server through an ODBC connection ... It is very puzzling because this client doesn't seem to have anything ...
    (microsoft.public.sqlserver.connect)
  • Re: netbios and named pipes sql replication.
    ... Once the client switched to named pipes, ... > connection with. ... > When we took named pipes support off the SQL server with the MS patch, ... > We had to manually walk thru the ODBC setup and configure the CLIENT ...
    (microsoft.public.sqlserver.replication)
  • Re: Problem connection to SQL Server with ODBC
    ... What protocols are being used by the client and what is the ... Are you using an alias for the connection? ... SQL Server Clients May Change Protocols When They Try ...
    (microsoft.public.sqlserver.connect)
  • Re: General Network Error
    ... connection, but you are not properly prepared to reopen a connection when ... retrieves data from a Sql Server 2000 instance, manipulates that data, ... The app works fine on the emulator during development. ... "General Network error. ...
    (microsoft.public.dotnet.framework.compactframework)