Multiple processes have roughly the same logintime in sysprocesses

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Author - 30 Jun 2008 18:47 GMT

I have a .net 2.0 web application against SQL Server 2000.

I was interested in peeking into the database and see the activities
of this web application. So, I issued this following query in SQL
Analyzer (pretty much like sp_who[2]):

select
spid,
dbid,
convert(varchar(30), login_time, 109) as logintime,
status,
hostname,
program_name,
cmd,
loginame
from sysprocesses
where loginame='my_username'

And I see this (which is a screenshot):

http://gnewsgroup.googlepages.com/sleepingprocesses

Please note in the query result that 8 processes have roughly the same
logintime value (around 11:11 AM today).

This web application has only around 10 users and it is impossible
that 8 of them are trying to log into the application at the same
time.

So, I am wondering if this is the so-called connection leak? In any
case, please give me hint about what might be going on and if this
will be a concern if I had hundreds of users. Thank you very much.

Reply to this Message

Andrew J. Kelly - 30 Jun 2008 20:55 GMT

It is most likely the result of connection pooling. This will create a
series of connections in a pool that the users will use when they connect.
The connection is then cleaned up and ready for a new user when they
disconnect. But it will leave xx many connections live all the time
depending on the settings of the connection pooling and the number of
concurrent users.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>I have a .net 2.0 web application against SQL Server 2000.
>
[quoted text clipped - 28 lines]
> case, please give me hint about what might be going on and if this
> will be a concern if I had hundreds of users. Thank you very much.

Reply to this Message

Author - 30 Jun 2008 22:47 GMT

On Jun 30, 3:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
wrote:

> It is most likely the result of connection pooling. This will create a
> series of connections in a pool that the users will use when they connect.
[quoted text clipped - 39 lines]
> > case, please give me hint about what might be going on and if this
> > will be a concern if I had hundreds of users. Thank you very much.

Thank you very much. One of our .net 1.1 web application's login page
shows us this message these days:

Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached.

And no one can log in.

When I issue

sp_who[2]

with SQL Analyzer, I see about 140 sleeping connections having the
same host process ID and same loginame (Not sure why M$ doesn't call
it loginname instead). This loginame is exactly what we have in the
web application's web.config file for the connectionString element.

If we kill all of these 140 or so sleeping processes, it does not seem
to help immediately. But eventually, it seem to clear up that error
message of the web application and people can login.

Any thought on this? Thank you.

Reply to this Message

Andrew J. Kelly - 01 Jul 2008 01:06 GMT

It sounds like you have troubles with the app not cleaning up after itself
and not closing each connection. As such when the initial pool size is
reached it will open x many more connections each time until it reaches the
max as defined in the config for connection pooling. Once you close (or
kill in your case) enough connections the pool size will drop after a few
minutes. This is all normal behavior for connection pooling. But as I
stated it is the app that is causing the problems not SQL Server or the
connection pooling itself. The app needs to close the connections when it is
done and it is apparently not doing that.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

On Jun 30, 3:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
wrote:

> It is most likely the result of connection pooling. This will create a
> series of connections in a pool that the users will use when they connect.
[quoted text clipped - 43 lines]
> > case, please give me hint about what might be going on and if this
> > will be a concern if I had hundreds of users. Thank you very much.

Reply to this Message