Losing connections with SQL 2005

We have a VB6 application that I recently converted over from SQL Server 200
0
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,Intermittent connectivity issues are generally difficult to
troubleshoot. That being said...what do you define as a
"lost connection". What are the errors? What errors do the
clients receive? What errors are in the logs? Have you run
profiler traces to try to get more information on what may
be going on?
Does the application keep connections open for some reason
after processing a database request?
-Sue
On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>We have a VB6 application that I recently converted over from SQL Server 20
00
>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,|||Hi Sue,
The error number reported is either 3604 or 16389 from Microsoft OLE DB
provider for SQL Server.
The best way I can describe "lost connection" is that you can no longer do
Connection.Execute or pass the connection object to Recordset.Open.
The behaviour is exactly as if you unplugged a network cable and then
replugged it. In fact I have been doing this to try and develop error
handling code to recover from the situtation.

> Does the application keep connections open for some reason
> after processing a database request?
As I mentioned our application does not use connection pooling and therefore
we obviously keep the connection open after a database request. Each client
application maintains two connections for the lifetime of the application.
One connection is a read-only connection, the other is a read write. Our
target customer is less than 50 users, so keeping a 100 connection objects
open is not a problem for the server.
My problem is that SQL 2005 seems to have much more fragile database
connections than SQL 2000 did. I was just wondering if there were some SQL
settings that could increase the robustness.
Thanks,
Darrel
"Sue Hoegemeier" wrote:
> Intermittent connectivity issues are generally difficult to
> troubleshoot. That being said...what do you define as a
> "lost connection". What are the errors? What errors do the
> clients receive? What errors are in the logs? Have you run
> profiler traces to try to get more information on what may
> be going on?
> Does the application keep connections open for some reason
> after processing a database request?
> -Sue
> On Fri, 9 Feb 2007 13:36:01 -0800, Darrel Miller
> <DarrelMiller@.discussions.microsoft.com> wrote:
>
>|||On Tue, 13 Feb 2007 09:04:01 -0800, Darrel Miller
<DarrelMiller@.discussions.microsoft.com> wrote:

>Hi Sue,
>The error number reported is either 3604 or 16389 from Microsoft OLE DB
>provider for SQL Server.
>The best way I can describe "lost connection" is that you can no longer do
>Connection.Execute or pass the connection object to Recordset.Open.
Then that's an application issue or address from the
application end. Those are things you see in the app not in
SQL Server.

>As I mentioned our application does not use connection pooling and therefor
e
>we obviously keep the connection open after a database request.
That doesn't necessarily mean you would or should keep two
connections open for the lifetime of the application but
that's a whole different thing.

>My problem is that SQL 2005 seems to have much more fragile database
>connections than SQL 2000 did. I was just wondering if there were some SQL
>settings that could increase the robustness.
Part of the problem in try to address it from a SQL Server
end is that you can't really quantify it. It's not real
clear what data access changes may have happened with the
application either - did you also switch over to ADO.Net,
change MDAC versions, drivers/providers? You need to look at
network, OS, potential issues as well.
-Sue|||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@.discussions.microsoft.com> wrote in message
news:46643664-0C8E-4C94-8DE4-4704518EAE8A@.microsoft.com...
> 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,
>|||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@.ninprodata.com> wrote in message
news:uAzf37ibHHA.2088@.TK2MSFTNGP04.phx.gbl...
> 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@.discussions.microsoft.com> wrote in message
> news:46643664-0C8E-4C94-8DE4-4704518EAE8A@.microsoft.com...
>|||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@.betav.com> wrote in message
news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
> 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@.ninprodata.com> wrote in message
> news:uAzf37ibHHA.2088@.TK2MSFTNGP04.phx.gbl...
>|||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@.ninprodata.com> wrote in message
news:Oa3%2321nbHHA.2300@.TK2MSFTNGP06.phx.gbl...
> 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@.betav.com> wrote in message
> news:OaM5uelbHHA.4808@.TK2MSFTNGP04.phx.gbl...
>