Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Interesting Finds

Jason Haley — Thu, 09 Mar 2006 13:48:28 GMT

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

wesclark — Tue, 21 Mar 2006 07:54:59 GMT

We are running automated tests that intentionally drop all the connections to the database and make them again. If the tests run quickly, we are disconnecting and connecting very frequently. We notice that after the connection is closed (this is through the JDBC driver, but I'm not sure that matters), the connection port stays open in a wait state for several minutes. If the number gets too high (about 4000), the range of ports is used up, and the tests start to fail.

Is there a way to have the TCP/IP port be released on the client immediately, or at least sooner? Is there a way to specify a large range of ports?

By change the keep alive setting, do you think the TCP/IP port would close sooner?

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Matt Neerincx [MSFT] — Tue, 21 Mar 2006 20:41:30 GMT

Take a look at this article, it explains the issue pretty well:

328476 Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328476

KeepAlive does not help.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

wesclark — Tue, 21 Mar 2006 22:57:29 GMT

Yes. I found that KB article after posting here. Thanks.

"Keep alive" problem / not working

.agony — Wed, 24 May 2006 17:51:10 GMT

We tried this feature with no success - neither the standard properties nor slightly changed properties worked.

We tested like this:
SQL Server 2005 - ADO.NET Client
The client established an explicit lock on one row at one db. Afterwards we disconnected the client by pulling out the network-cable. We waited about 35 sec for the sessios to close - but nothing happend; we waited another minute but nothing changed. The command line "netstat" told us that the connections are alive ... so what went wrong? Did we miss something? btw we installed the sp1 before all tests!

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Nan Tu — Wed, 24 May 2006 21:49:38 GMT

Did you restarted server after change the setting?
Can you ping the server after you unplug the network cable?
The ultimate way to monitor if the keepalive is working is to get a netmon trace. You should be able to see the keepalive request and response on a idle connection or only keepalive request on a broken connection.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

.agony — Fri, 26 May 2006 10:02:44 GMT

Yes, we did "serveral* restarts to ensure everything is loaded correctly.
Of course we can't ping the server - the cable is unplugged!

We will test the keep-alive-packages with netmon. I'll post my results!

Thanks

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

.agony — Fri, 26 May 2006 13:54:48 GMT

Ok, we testet if acknowledge-keep-alive-packages were sent.
The answer is: NO

The server behaves like the client computer is online. It sends packages to the client with no responds, but did not disconnect the session.

So, what could be the problem source? A missing service? Misconfiguration of the TCP protocol? Something else?

Suggestions?

Meanwhile: thanks fot the assistance!
.agony

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Nan Tu — Fri, 26 May 2006 22:19:46 GMT

"It sends packages to the client with no responds, "

[nantu]This looks correct behavior if you unplug the calbe.

"but did not disconnect the session. "
[nantu] can you see if the session is still alive by following query,

"select session_id from sys.dm_exec_connections" to see the the session is closed.

Otherwise, what is your OS version, is it xpsp2 or w2k3sp1?

Do you have any third party antivirus software? If so, can you try without them.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

.agony — Mon, 29 May 2006 10:55:17 GMT

yes, the connections are still alive but no keep-alive packages are send to the client computers.
Currently my test server is on a xpsp2 computer with EZ Antivirus installed. Hopefully i get an w2k3sp1 server within the next two days.
Now I'll try to disable the antivirus software and post the results

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

.agony — Mon, 29 May 2006 11:58:07 GMT

ok, disabling the antivirus software was no solution. no different behaviour.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

.agony — Tue, 30 May 2006 09:47:41 GMT

some other ideas?

Długotrwałe blokady a rozłączone sesje

.:: strefa ::. — Wed, 02 May 2007 18:44:00 GMT

Transakcyjność zgodna z założeniami wyrażonymi w popularnym akronimie ACID (Atomicity, Consistency, Isolation, Durability) w warunkach współbieżności i konkurencyjności o zasoby wymaga od silnika bazodanowego odpowiedniej obsługi blokad zasobó

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

cory — Wed, 11 Jul 2007 20:15:52 GMT

Hi,

The article mentions that the tcp/ip keep-alive is NOT on by default on windows OS, but sql server uses it. Is there a way to turn off tcp/ip keep-alive for SQL Server 2005? Was tcp/ip keep-alive on or off for sql server 2000?

Thanks!

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Simon — Wed, 01 Aug 2007 08:29:34 GMT

When does the sql server issue the keepalive? Is it when it just finishes answering a query?

Thanks.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Nan Tu — Wed, 01 Aug 2007 20:22:02 GMT

Keeplive is a TCP feature that SQL Server leverages to detect transport layer failure. In default setting of Windows operating system, it issues keepalive 30 seconds after the connection becomes idle.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

pvr_in_msdn — Thu, 31 Jul 2008 18:54:07 GMT

Hi Nan Tu,

What are the settings we need to enable KeepAlive through SqlServer2005. I know there is an option to configure thru Sql server Configuration Manager TCP/IP options. But is that fine or we need to set someting other than that. Please clarify.

Thanks in Advance,

Palani

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

SQL Protocols — Sat, 02 Aug 2008 01:10:00 GMT

Hi Palani,

Sorry for the lack of clarity in the blog post. SQL Server 2005 enables KeepAlive on all of its connections, and that is sufficient to put KeepAlive to use - there is no need to enable it elsewhere in the OS. Other than to use SQL Server Configuration Manager to potentially change the parameters mentioned above, there should be no configuration necessary to get KeepAlive.

Hope this helps!

Dan Benediktson

SQL Protocols

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

praveen — Thu, 07 Aug 2008 16:26:51 GMT

We have problem with SQL 2005 in our company. A script is being run to extract the data from SQL server and sometimes server drops the connection when script runs for more than an hour.

Is there any setting that I can change so that connection will not be dropped.

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Peter — Fri, 10 Oct 2008 02:30:16 GMT

How to use netmon to trace keepalive packets?

Timeouts von SqlCommand vermeiden - Seite 2 | hilpers

Timeouts von SqlCommand vermeiden - Seite 2 | hilpers — Tue, 20 Jan 2009 19:18:23 GMT

PingBack from http://www.hilpers.com/1025277-timeouts-von-sqlcommand-vermeiden/2

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

B. H. — Mon, 13 Apr 2009 21:32:10 GMT

some questions on this topic.

Does it need to bounce the sql service after reconfig "keep alive" numbers in Sqlserver configuration manager?

Do any of these window parameters, KeepAliveInterval and KeepAliveTime, should be set as well, as they don't seem to come with default setting in registry? In other words, if we only config sqlserver side, this feature will take effect as well?

Also heard a workaround to fix keealive problems by setting "Keep Alive" in sql server to 0, instead of default 30000. By doing this, is keepalive still working?

Thanks so much

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

David — Thu, 23 Jul 2009 23:56:04 GMT

So what are the implications of increasing this number, lets say to an hour? Doesn't that mean I could potential run into port exhaustion or something on a busy system?

re: Understand special TCP/IP property “Keep Alive” in SQL Server 2005

Amandeep — Wed, 16 Sep 2009 16:39:03 GMT

Hi,

Sometimes we get this error below when our application tries to write to database.

ODBC ERROR: Thursday July 16 2009 16:51: 1

SQLState[01000] NativeError[10054]

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).

Can this be due to 'keep Alive' settings on sql server 2005. Can you please throw some light on this error? we really appreciate any response from you.

Thanks

-Amandeep