SQL Server JDBC - Running out of network ports

Author

Running out of network ports

Wes Clark

2006-03-21, 3:24 am

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?

To see the problem, write a small program that does nothing more than open
and close a connection in a loop. After about 4000 connects and disconnects,
the program will fail.

Marco Trevisan

2006-03-21, 3:24 am

"Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message news:5CC577F9-4665-45E4-8989- B888C7F582DE@microso
ft.com...
> 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?

This article refers to Windows Server 2003 SP1 (not precisely a client)
"...Once the TIME WAIT state is reached, TCP must wait twice the maximum
segment lifetime (MSL) before a connection with the same set of socket addresses
can be created.
....You can mitigate this situation by setting the TcpTimedWaitDelay registry entry at
HKEY_LOCAL_MACHINE\S
ystem\CurrentControl
Set\Services\Tcpip\P
arameters"
http://www.microsoft.com/technet/co...guy/cg1204.mspx

Never tried and never investigated for a solution on another platform.

--
Marco

*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

Wes Clark

2006-03-21, 11:23 am

You are right. There are actually two TCP/IP parameters that can be changed
to mitigate the problem.

I found a MS KB article (http://support.microsoft.com/kb/328476) which
describes the problem exactly as a symptom you’ll see if you aren’t using a
connection pool and are rapidly opening and closing connections, which is the
practical effect of our test suites. SQL Server specifically does not reuse
ports because of a security concern. That’s why these things build up.

The solution is to increase the number of available user ports from 4000 to
approximately 65,000, the maximum. I did this on my machine, and was able to
create and destroy 10,000 SQL Server connections without an error.

Another setting that can be changed is the time for the TCP/IP port to die.
It defaults to four minutes. I decided not to try changing this one. The
article said the parameter should already be in the registry, and it wasn’t
in mine.

KB article http://support.microsoft.com/kb/Q319502 is actually the one with
the instructions.

"Marco Trevisan" wrote:

> "Wes Clark" < WesClark@discussions
.microsoft.com> wrote in message news:5CC577F9-4665-45E4-8989- B888C7F582DE@microso
ft.com...
>
> This article refers to Windows Server 2003 SP1 (not precisely a client)
> "...Once the TIME WAIT state is reached, TCP must wait twice the maximum
> segment lifetime (MSL) before a connection with the same set of socket addresses
> can be created.
> ....You can mitigate this situation by setting the TcpTimedWaitDelay registry entry at
> HKEY_LOCAL_MACHINE\S
ystem\CurrentControl
Set\Services\Tcpip\P
arameters"
> http://www.microsoft.com/technet/co...guy/cg1204.mspx
>
> Never tried and never investigated for a solution on another platform.
>
> --
> Marco
>
>
> *** Free account sponsored by SecureIX.com ***
> *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
>

Evan T. Basalik

2006-03-28, 9:23 am

We actually see this issue a lot when people go from Windows 2000 to Windows 2003. On Windows 2000, we were a little bit lax about enforcing the four
minute default lifetime. On Windows 2003, we enforce it much more rigourously, so if you open a whole bunch of connections in a short time period, you can
run out of ports on the client machine.

Changing the two registry keys as described in KB328476 is the correct way to go.

Evan

--------------------
>Thread-Topic: Running out of network ports
>thread-index: AcZM9PqMFwTwbIHsRjeO
Qtid+vb5pQ==
>From: =?Utf-8?B?V2VzIENsYXJr?= < WesClark@discussions
.microsoft.com>
>References: <5CC577F9-4665-45E4-8989- B888C7F582DE@microso
ft.com> < 441fb7bd$0$944$6d36a
cad@titian.nntpserver.com>
>Subject: Re: Running out of network ports
>Date: Tue, 21 Mar 2006 06:37:28 -0800
>
>You are right. There are actually two TCP/IP parameters that can be changed
>to mitigate the problem.
>
>I found a MS KB article (http://support.microsoft.com/kb/328476) which
>describes the problem exactly as a symptom you’ll see if you aren’t using a
>connection pool and are rapidly opening and closing connections, which is the
>practical effect of our test suites. SQL Server specifically does not reuse
>ports because of a security concern. That’s why these things build up.
>
>The solution is to increase the number of available user ports from 4000 to
>approximately 65,000, the maximum. I did this on my machine, and was able to
>create and destroy 10,000 SQL Server connections without an error.
>
>Another setting that can be changed is the time for the TCP/IP port to die.
>It defaults to four minutes. I decided not to try changing this one. The
>article said the parameter should already be in the registry, and it wasn’t
>in mine.
>
>KB article http://support.microsoft.com/kb/Q319502 is actually the one with
>the instructions.
>
>
>"Marco Trevisan" wrote:
>
>

--

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this message are best directed to the newsgroup/thread from which they originated.

Sponsored Links