Multiple processes have roughly the same logintime in sysprocesses
in [Server Configuration]
|
From: Author on 30 Jun 2008 13:47 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.
From: Andrew J. Kelly on 30 Jun 2008 15:55 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. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Author" <gnewsgroup(a)gmail.com> wrote in message news:a5f301fc-c73b-401b-9bb0-bfd897042271(a)y21g2000hsf.googlegroups.com... >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.
From: Author on 30 Jun 2008 17:47 On Jun 30, 3:55 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)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.. > 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. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Author" <gnewsgr...(a)gmail.com> wrote in message > > news:a5f301fc-c73b-401b-9bb0-bfd897042271(a)y21g2000hsf.googlegroups.com... > > >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. 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.
From: Andrew J. Kelly on 30 Jun 2008 20:06 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. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Author" <gnewsgroup(a)gmail.com> wrote in message news:5ac39980-57ca-48ab-97e8-61fb7be11d98(a)a1g2000hsb.googlegroups.com... On Jun 30, 3:55 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)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. > 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. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "Author" <gnewsgr...(a)gmail.com> wrote in message > > news:a5f301fc-c73b-401b-9bb0-bfd897042271(a)y21g2000hsf.googlegroups.com... > > >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. 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.
|
Pages: 1 Prev: Object Explorer Details Next: Help needed - Data Formatting |