open_tran = 1

We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.Doesn't matter if you COMMIT or not, if you continuely open another one,
then you'll see it open.
Here's what I've seen when our developer's have used JDBC drivers. They
continually SELECT 1 to keep their pooled connections open. They use SET
IMPLICIT TRANSACTIONS ON, which automatically starts transactions without
having to explicitly use the BEGIN TRANSACTION statement. Problem is that
they start on most T-SQL statements, including SELECT.
Sincerely,
Anthony Thomas
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102791403.549396.160930@.z14g2000cwz.googlegroups.com...
We use i-net sprinta jdbc driver and use pooled connections. What I've
noticed is that when connections are not being used and are in sleeping
state Open_Tran in the sysprocesses table is 1 and not 0. Anyone know
why? We commit our transactions.|||Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?|||Yep, but I would be worried about that many concurrent connections, open
transactions or not. Are you running the 32-bit or 64-bit installation?
I ask because 32-bit has a serious memory drawback. Although the higher end
stuff can create a very nice Buffer Pool with AWE, all of the other memory
objects must be resident in the 2 GB--or 3 GB, depending on versions,
editions, and parameters--USER MODE address space. Moreover, the conection
structures are part of what lives in the MEM TO LEAVE region; so, you are
even more confined.
Now, you add object lock structures and multiple concurrent open
transactions and you are inviting disaster. I would seriously look at going
multi-instanced or the 64-bit installation, with a TON OF MEMORY, or course.
Sincerely,
Anthony Thomas
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1102990516.435766.91180@.c13g2000cwb.googlegroups.com...
Anthony,
You are right. Now my question is does this have any negative
consequences to performance. We are an asp with 100s of databases on
our sql 2000 servers, each with there own pooled connections. I have on
average over 3000 - 4000 of these connections always in an open
transaction state?