Connecting to SQL server

[Ryan]

We have two applications (1 on two servers and 1 on another). Every so
often, we 'lose' the connection to the SQL database which in effect
takes these applications down. One app is a service (written in Delphi)
, the other is a web page (ASP).

From what I can tell, both different methods use the same port (1433)

to access the data and create an open connection. We can telnet to this
before a problem and suspect that we can't afterwards.

I can however, connect through EM (I really do need to confirm if this
remains available) and I can see a trace running on the SQL server even
when the problems occur (the trace is to see if SQL is still doing
something). I am under the impression that the SQL server is fine, but
does anyone know what port EM and SQL Profiler use ? If it's something
different, then that would explain some of it.

I think there is a network problem on this specific port, but that
others are OK. I'm a bit rusty on this, so please be gentle with any
replies. :-)

Thanks
Ryan

[ggrater]

Although it might be a network issue, if Profiler and EM are still
running, it's likely not. The issue may be the connection management
in the Delphi and ASP application. Connections from host applications
should be opened, used and immediately closed in order to be available
for other users. If too many connections are being left open, at some
point, you'll run out. Since Profiler and EM already have a
connection, they still function normally.

I would look at the connection management in the Delphi and ASP
applications and maybe do an sp_who2 when things go sideways to see how
many connections are open.

BTW...EM and Profiler use the same type of connection the ASP and
Delphi application use and therefore, they are all talking to SQL
Server on the same port, 1433.

[Erland Sommarskog]

Ryan (ry********@hotmail.com) writes:

We have two applications (1 on two servers and 1 on another). Every so
often, we 'lose' the connection to the SQL database which in effect
takes these applications down. One app is a service (written in Delphi)
, the other is a web page (ASP).

From what I can tell, both different methods use the same port (1433)
to access the data and create an open connection. We can telnet to this
before a problem and suspect that we can't afterwards.

I can however, connect through EM (I really do need to confirm if this
remains available) and I can see a trace running on the SQL server even
when the problems occur (the trace is to see if SQL is still doing
something). I am under the impression that the SQL server is fine, but
does anyone know what port EM and SQL Profiler use ? If it's something
different, then that would explain some of it.

I think there is a network problem on this specific port, but that
others are OK. I'm a bit rusty on this, so please be gentle with any
replies. :-)

Profiler and EM and everyone else operates on the same port. Unless,
they are on the same machine as SQL Server, in which case they
typically use Shared Memory?

And in any case, network problems usually to not conern a single
port.

On what machines are EM and Profiler?

To me it sounds like you have glitches in your network. A short drop
in the connection may be fatal for a tool that does not handle it
well. (Which for instance Query Analyzer does. EM may also be
reconnecting silently, if it finds the connection dead.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ryan]

Yes, I think it is a network problem but it's a case of trying to prove
it and rule out all other options.

We'll be moving away from this hosted service soon, but it's a case of
making the best of what we have until we move away.

R

[pb648174]

We have two clients which have this same problem and have never been
able to figure it out.

As Erland said, Query Analyzer being open doesn't mean anything. I can
open Query Analyzer to a server over a VPN connection, drop the VPN
connection and then reconnect and keep using Query Analyzer, no
problem. You would have to be attempting to actually execute a query
every few seconds to actually know that Query Analyzer or EM is not
experiencing the same issue.

Ryan wrote:

Yes, I think it is a network problem but it's a case of trying to prove
it and rule out all other options.

We'll be moving away from this hosted service soon, but it's a case of
making the best of what we have until we move away.

R