Re: SQL 2005 orphaned connections

Charles,
The "Keep Alive" property is mentioned in the SQL BOL twice. Once under "SQL Server Configuration Manager Help --> SQL Server 2005 Network Configuration -->TCP/IP Properties (Protocols Tab)" and once under "SQL Server Configuration Manager Help --> SQL Native Client Configuration --> Client Protocols (SQL Server Configuration Manager) --> Client Protocols - TCP/IP Properties (Protocols Tab)".

It would suggest to me that the "keep alive" setting for the Server Network Configuration relates to how often the server pings the client. The definition was only added in SP1 (noted in the Change History). Also, the "Windows TCP Implementation Features" article on MSDN (http://msdn2.microsoft.com/en-us/library/ms819735.aspx) states that an application could request "keep alive" reqests to be sent on a particular connection without having them enabled on all TCP/IP connections. This would certinly be preferable as we do not know what impact enabling "keep alive" requests on all connections of all applications would be (especially the DTC and MSMQ).

Also, the KB article you refer to is for SQL 2000. SQL 2000 had no mention of "Keep Alive" requests built into the application. Why would this become something that is included in SQL 2005 settings/documentation if it still was not a feature?

Sorry to keep bothering you, but I still think this is a feature that isn't working correctly in our environment rahter than a feature that doesnt exist.



Andrew

"Charles Wang[MSFT]" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message news:GcwxNt8nIHA.8872@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi Andrew,
Thank you for your response.
They should not conflict with each other. The KB article is for server
side, while the document you saw in BOL should be for client side. In SQL
Server 2005 BOL, it describes Keep Alive under TCP/IP protocol as the
following:
"Keep Alive
This parameter (in milliseconds) controls how often TCP attempts to verify
that an idle connection is still intact by sending a KEEPALIVE packet. The
default is 30000 milliseconds.".

As you can see it does not mention that SQL Server will send keep-alive
packet, instead SQL Server 2005 depends on the underlying TCP layer to send
keep-alive packages. This is related to working with underlying network
driver. I am not sure how SQL Server implements it since it is
undocumented. According to the KB article, I think that it may still rely
on Windows OS communicating with the underlying networking driver. Compared
with SQL Server 2000, SQL Server 2005 just provides an user interface so
that any user can configure this parameter according to his requirement. As
far as I know that keep alive is naturally implemented in TCP/IP protocol
since TCP/IP is connection oriented. There must be connection retrying
mechanism.

The Keep Alive Interval you saw is for client protocol and only used in SQL
Native Client. This is not at server side. Since SQL Native Client is a
driver program, it may be able to directly manipulate TCP protocol at
driver level.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.



Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================






.

Relevant Pages

  • RE: MS SQL & Msg 11, Level 16, State 1
    ... General Network Errors are one of the most difficult to troubleshoot, ... Connectivity problems can occur if a firewall times out a connection before ... the client and SQL are done with it. ... Is there very heavy load on the SQL server when this happens? ...
    (microsoft.public.sqlserver.connect)
  • Re: Problem with NetScreen Remote 7.0.3 over DSL connection when accessing SQL....
    ... The trick to this one is making the SQL server think the remote client ... At present the SQL server see's the public NATTed address of your DSL. ... >From Dialup VPN ... > the network it will take about 5-10 minutes just for the login screen ...
    (comp.security.firewalls)
  • Re: Intermittent connection
    ... "Tay" wrote in message ... > I can ping the sql server machine from the client terminal. ... can you run a ping comand to the SQL Server ... >> THis type of errors will come only if there is a slow network. ...
    (microsoft.public.sqlserver.server)
  • Re: sql server2000; TCP/IP vs Named Pipes
    ... wireshark.org have an excellent protocol analyser. ... My company is running an application that uses sql server 2000 sp3. ... We prefer to use TCP/IP but we are having issues with users losing there ... How can I isolate the problem to th network? ...
    (microsoft.public.sqlserver.tools)
  • RE: SQL and Bandwidth use
    ... Not an area I know much about but if it's not the network card then maybe ... The client has to receive a buffer then tell the ... While monitoring total bandwidth on segment via ... > takes a very long time to transmit the data from the SQL server, ...
    (microsoft.public.sqlserver.server)