SQL Server JDBC - Problem with JDBC -- Eating up Ports/Processes!

Author

Problem with JDBC -- Eating up Ports/Processes!

Orwell

2006-02-15, 8:23 pm

We are using JDBC to connect to a SQL Server 2000 database using JDBC.
We're having an odd problem, and I cannot find anybody else who is
having this issue. The application server machine (WAS 6.0) is eating
up ports and generating SQL Server processes like there is no tomorrow.

SQL Server is listening on port 1433, but if I do a "netstat" I see
something like this:

TCP 127.0.0.1:4479 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4480 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4481 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4482 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4483 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4484 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4485 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4486 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4487 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4488 127.0.0.1:1433 ESTABLISHED
TCP 127.0.0.1:4489 127.0.0.1:1433 ESTABLISHED

There are dozens -- sometimes hundreds, thousands -- of these
connections. On SQL Server, each of these connections is generating a
SQL Server process (visible under "Process Info" in Enterprise Manager)
with a status of "sleeping." Eventually, these processes "go away" if
there's no activity, but if not, the connections eventually choke the
machine and we need to reboot.

I know we're not generating excess connections, because if I change the
JDBC driver to DB2, we only get one connection.

Does anybody have any idea what might be happening? Is there a SQL
Server or JDBC setting I am not aware of? Any help would be
appreciated, we're going nuts over here!!!

Thanks in advance!

Joe Weinstein

2006-02-15, 8:23 pm

Orwell wrote:

> We are using JDBC to connect to a SQL Server 2000 database using JDBC.
> We're having an odd problem, and I cannot find anybody else who is
> having this issue. The application server machine (WAS 6.0) is eating
> up ports and generating SQL Server processes like there is no tomorrow.
>
>
> SQL Server is listening on port 1433, but if I do a "netstat" I see
> something like this:
>
> TCP 127.0.0.1:4479 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4480 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4481 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4482 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4483 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4484 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4485 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4486 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4487 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4488 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4489 127.0.0.1:1433 ESTABLISHED
>
> There are dozens -- sometimes hundreds, thousands -- of these
> connections. On SQL Server, each of these connections is generating a
> SQL Server process (visible under "Process Info" in Enterprise Manager)
> with a status of "sleeping." Eventually, these processes "go away" if
> there's no activity, but if not, the connections eventually choke the
> machine and we need to reboot.
>
> I know we're not generating excess connections, because if I change the
> JDBC driver to DB2, we only get one connection.
>
> Does anybody have any idea what might be happening? Is there a SQL
> Server or JDBC setting I am not aware of? Any help would be
> appreciated, we're going nuts over here!!!
>
> Thanks in advance!

Which driver are you using? If it's the old one, try adding
the property "selectMethod=cursor" to the connection properties,
or try the new SQL2005 driver (as long as it will connect to SQL2000)
let us know...
I suspect that your application code is not closing statements
asap...
Joe Weinstein at BEA Systems

Wes Clark

2006-03-21, 3:24 am

We are experiencing almost the exact same symtpom, with the 2005 driver. The
"netstat -a" command is showing their status as WAIT, instead of ESTABLISHED.
We tried swapping in the jTDS driver, but that didn't help. If running a
lot of fast running automated tests, the machine can actually run out of
connections. We're trying to find why, assuming it's an unclosed result set,
but don't know yet.

"Orwell" wrote:

> We are using JDBC to connect to a SQL Server 2000 database using JDBC.
> We're having an odd problem, and I cannot find anybody else who is
> having this issue. The application server machine (WAS 6.0) is eating
> up ports and generating SQL Server processes like there is no tomorrow.
>
>
> SQL Server is listening on port 1433, but if I do a "netstat" I see
> something like this:
>
> TCP 127.0.0.1:4479 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4480 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4481 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4482 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4483 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4484 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4485 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4486 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4487 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4488 127.0.0.1:1433 ESTABLISHED
> TCP 127.0.0.1:4489 127.0.0.1:1433 ESTABLISHED
>
> There are dozens -- sometimes hundreds, thousands -- of these
> connections. On SQL Server, each of these connections is generating a
> SQL Server process (visible under "Process Info" in Enterprise Manager)
> with a status of "sleeping." Eventually, these processes "go away" if
> there's no activity, but if not, the connections eventually choke the
> machine and we need to reboot.
>
> I know we're not generating excess connections, because if I change the
> JDBC driver to DB2, we only get one connection.
>
> Does anybody have any idea what might be happening? Is there a SQL
> Server or JDBC setting I am not aware of? Any help would be
> appreciated, we're going nuts over here!!!
>
> Thanks in advance!
>
>

Sponsored Links