SQL Server Performance Forum – Threads Archive

100 connections by 1 user

Hi !
I notice that 1 user ( and i am sure this is one person)will open up to 100 connections to sql server
When i look at those connections in current ativity it shows this statement curently executed by this user:
IF @@TRANCOUNT > 0 COMMIT TRAN I made a conclusion for myself that developers do not close connectoins
Am i right?
What else in this situation can point to problem?

I’m not a development, but I agree with you about not close connections
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I know of certain applications that map all application users to a single SQL login. Though it appears as one user opening multiple connections, it can be actually multiple users accssing database with single SQL login.

how do i find out for how long this connection exists?

May be Profiler, Objects, Object<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />pen, Closed, filter by particular user? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell

Either the connections are not closed or the app could be using slow serverside cursors forcing new connections to create a new connection in the pool instead of using existing ones. Do a profiler trace and check for sp_cursorfetch and as mentioned above with connection open and close. It could aslo be an app that defines a hardcoded value for the number of connections to open by default in the pool. You often do this with java applications. If the number is always exactly 100 then one could suspect this but it shouldn’t really be a problem unless you are low on memory. /Argyle

Hi !
Developer that create that app saing that probably it orphaned connections .
And we have to restart the server to remove them.
For me it sounds like a very poor code…but i have to deal with that somehow.
How do i find out which process is orphant?

Connections were form 1 week to 3 days old. Under any circumstances is that possible in good app?

Hi NewDBA, It seems to me that somewhere in the code there is a connection that is not being closed. I see this happening in a client’s (not ours) server that runs an application which handles connections poorly. It’s a small application with tiny database (1Mb) and they somehow manage to arrive (in a week) to 300 connection. developer is full of excuses and explanation about connection pooling and stuff, and end up killing them one by one instead of finding the source of the problem <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Why don’t you try to run a trace and see what statements run before this IF @@TRANCOUNT > 0 COMMIT TRAN. The fact that the statement is always the same leads me to believe that there is a certain procedure that is causing it. Unless they are using this transactions all over the application… Bambola.

Why should i check statement just before this statment:"IF @@TRANCOUNT > 0 COMMIT TRAN
why not exactly this statement?

Corrections<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />p_who2 will return when lastbatch was executed .So this is how to find out haw old are connections

YOu can get those details from sysprocesses table for login_time column. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

So practicly there cannot be any external proceses that older that 24 hours?

If "IF @@TRANCOUNT > 0 COMMIT TRAN" is used in more than one function in the application (and it is very possible) but sometimes connection is closed and others not, than you will need to search for the previous statement to understand which of the functions issued the statement. Otherwise it’s enough to search for this one. Bambola.

I regularly(nightly) killing old connections on one server as the code according to developers ofthen do not close it.
the company that developed app looked for not closed connections 1 year ago and
came back to us saing that they didn’t find anything.So i had to create sp that do that workaround instead of app Now looking at other server i notice that 20 connections always there by the same user.
i asked developers of that app to check the code.and this is what they answered. This is the user id used by the Notes decs connection for moving information from notes to SQL they are connected 24 hours a day to allow for finance overtime etc. There are not options in the desk connection software for closing and opening connections only as required. So as you see it not always wrong if current activity keep in procees information
connectkion for a month .

]]>