ASP.Net SQLConnection leaving orphans

Discussion in 'ASP .Net' started by =?Utf-8?B?Q2F0YWRtaW4=?=, Feb 22, 2005.

  1. Hello, Everyone.

    I have a strange one here. I'm a DBA with relatively low program
    experience. Recently I was given an assignment to locate why our SQL Server
    has "orphaned" connections. The setup we have is an IIS 6 machine that uses
    a third party app designed with ASP .NET. The SQLConnection object is used
    by the program to connect up to the SQL database. The connection string is
    kept in a separate file so the programmers only have to refer to it by name
    rather than retyping all the info. They use connection pooling via the
    SQLConnection object, but the pooling doesn't seem to work all the time. We
    have what seems to be an inordinate amount of connections that aren't
    returning to the pool which we have to clean up every night.

    I've researched on Google and the programmers say they have done everything
    to check the code for bad connection issues. The only thing we can come up
    with is that the ASP Worker Thread process doesn't throw some connection
    threads back into the pool. We believe that the issue is caused when the
    users click the 'X' button on their browser windows instead of hitting the
    program's 'LOGOUT' button.

    Can anyone shed any further light on this problem? Has anyone else had this
    problem before? If so, can anyone point me in the direction of a solution?
    The only thing I can think of is throwing a connection timeout value on the
    connection, but I'm not sure if it should go on the SQL Server properties,
    the ASP .Net SQLConnection properties or the IIS Server properties. I've
    heard that if I do the timeout on SQL Server, I won't be able to put that
    connection in the pool. Any help would be greatly appreciated.

    Thanks!!!

    Catadmin
    =?Utf-8?B?Q2F0YWRtaW4=?=, Feb 22, 2005
    #1
    1. Advertising

  2. I noticed a strange absences in your message. You didn't mention whether or
    not you had made sure that all Connections are closed when the app is
    finished with them. This is one of those issues like "Is the plug plugged
    in?" that is overlooked because it is so simple.

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    ..Net Developer
    Neither a follower nor a lender be.

    "Catadmin" <> wrote in message
    news:...
    > Hello, Everyone.
    >
    > I have a strange one here. I'm a DBA with relatively low program
    > experience. Recently I was given an assignment to locate why our SQL
    > Server
    > has "orphaned" connections. The setup we have is an IIS 6 machine that
    > uses
    > a third party app designed with ASP .NET. The SQLConnection object is
    > used
    > by the program to connect up to the SQL database. The connection string
    > is
    > kept in a separate file so the programmers only have to refer to it by
    > name
    > rather than retyping all the info. They use connection pooling via the
    > SQLConnection object, but the pooling doesn't seem to work all the time.
    > We
    > have what seems to be an inordinate amount of connections that aren't
    > returning to the pool which we have to clean up every night.
    >
    > I've researched on Google and the programmers say they have done
    > everything
    > to check the code for bad connection issues. The only thing we can come
    > up
    > with is that the ASP Worker Thread process doesn't throw some connection
    > threads back into the pool. We believe that the issue is caused when the
    > users click the 'X' button on their browser windows instead of hitting the
    > program's 'LOGOUT' button.
    >
    > Can anyone shed any further light on this problem? Has anyone else had
    > this
    > problem before? If so, can anyone point me in the direction of a
    > solution?
    > The only thing I can think of is throwing a connection timeout value on
    > the
    > connection, but I'm not sure if it should go on the SQL Server properties,
    > the ASP .Net SQLConnection properties or the IIS Server properties. I've
    > heard that if I do the timeout on SQL Server, I won't be able to put that
    > connection in the pool. Any help would be greatly appreciated.
    >
    > Thanks!!!
    >
    > Catadmin
    Kevin Spencer, Feb 22, 2005
    #2
    1. Advertising

  3. Sorry.

    The programmers say they've closed all the connections after they are done
    pulling the data. They also say they've closed the datareader objects. They
    say they haven't closed anything within the finalize or destructor method,
    the connection string is the same because they are pulling it from the
    web.config file, the connection reset value is set to true, they drop any
    temporary objects and close all user defined connections.

    Of course, this is what they tell me they've checked before and that they
    have spent a lot of time going through the code to verify it all. Now it is
    being given to me so that I can give them some fresh perspective. Nothing
    I'm finding on the internet, though, is a lot different from any of this.

    Catadmin

    "Kevin Spencer" wrote:

    > I noticed a strange absences in your message. You didn't mention whether or
    > not you had made sure that all Connections are closed when the app is
    > finished with them. This is one of those issues like "Is the plug plugged
    > in?" that is overlooked because it is so simple.
    >
    > --
    > HTH,
    >
    > Kevin Spencer
    > Microsoft MVP
    > ..Net Developer
    > Neither a follower nor a lender be.
    >
    > "Catadmin" <> wrote in message
    > news:...
    > > Hello, Everyone.
    > >
    > > I have a strange one here. I'm a DBA with relatively low program
    > > experience. Recently I was given an assignment to locate why our SQL
    > > Server
    > > has "orphaned" connections. The setup we have is an IIS 6 machine that
    > > uses
    > > a third party app designed with ASP .NET. The SQLConnection object is
    > > used
    > > by the program to connect up to the SQL database. The connection string
    > > is
    > > kept in a separate file so the programmers only have to refer to it by
    > > name
    > > rather than retyping all the info. They use connection pooling via the
    > > SQLConnection object, but the pooling doesn't seem to work all the time.
    > > We
    > > have what seems to be an inordinate amount of connections that aren't
    > > returning to the pool which we have to clean up every night.
    > >
    > > I've researched on Google and the programmers say they have done
    > > everything
    > > to check the code for bad connection issues. The only thing we can come
    > > up
    > > with is that the ASP Worker Thread process doesn't throw some connection
    > > threads back into the pool. We believe that the issue is caused when the
    > > users click the 'X' button on their browser windows instead of hitting the
    > > program's 'LOGOUT' button.
    > >
    > > Can anyone shed any further light on this problem? Has anyone else had
    > > this
    > > problem before? If so, can anyone point me in the direction of a
    > > solution?
    > > The only thing I can think of is throwing a connection timeout value on
    > > the
    > > connection, but I'm not sure if it should go on the SQL Server properties,
    > > the ASP .Net SQLConnection properties or the IIS Server properties. I've
    > > heard that if I do the timeout on SQL Server, I won't be able to put that
    > > connection in the pool. Any help would be greatly appreciated.
    > >
    > > Thanks!!!
    > >
    > > Catadmin

    >
    >
    >
    =?Utf-8?B?Q2F0YWRtaW4=?=, Feb 22, 2005
    #3
  4. =?Utf-8?B?Q2F0YWRtaW4=?=

    Prodip Saha Guest

    Catadmin,
    Information you have provided is not enough to precisely comment on. All
    programmers say that- we did it and it is your fault. You have to provide
    the code segment that's pulling data from SQL database in order for us (the
    programmer community) make any judgement.

    ADO.Net does the connection pooling automatically and it reuses the
    connections as long as the connection string remain the same between the
    calls. Having said that - one must explicitly close the connection in the
    same context it was open.

    Thanks,
    Prodip Saha


    "Catadmin" <> wrote in message
    news:...
    > Hello, Everyone.
    >
    > I have a strange one here. I'm a DBA with relatively low program
    > experience. Recently I was given an assignment to locate why our SQL

    Server
    > has "orphaned" connections. The setup we have is an IIS 6 machine that

    uses
    > a third party app designed with ASP .NET. The SQLConnection object is

    used
    > by the program to connect up to the SQL database. The connection string

    is
    > kept in a separate file so the programmers only have to refer to it by

    name
    > rather than retyping all the info. They use connection pooling via the
    > SQLConnection object, but the pooling doesn't seem to work all the time.

    We
    > have what seems to be an inordinate amount of connections that aren't
    > returning to the pool which we have to clean up every night.
    >
    > I've researched on Google and the programmers say they have done

    everything
    > to check the code for bad connection issues. The only thing we can come

    up
    > with is that the ASP Worker Thread process doesn't throw some connection
    > threads back into the pool. We believe that the issue is caused when the
    > users click the 'X' button on their browser windows instead of hitting the
    > program's 'LOGOUT' button.
    >
    > Can anyone shed any further light on this problem? Has anyone else had

    this
    > problem before? If so, can anyone point me in the direction of a

    solution?
    > The only thing I can think of is throwing a connection timeout value on

    the
    > connection, but I'm not sure if it should go on the SQL Server properties,
    > the ASP .Net SQLConnection properties or the IIS Server properties. I've
    > heard that if I do the timeout on SQL Server, I won't be able to put that
    > connection in the pool. Any help would be greatly appreciated.
    >
    > Thanks!!!
    >
    > Catadmin
    Prodip Saha, Feb 22, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bob
    Replies:
    11
    Views:
    2,136
    Angel Saenz-Badillos[MS]
    Apr 12, 2004
  2. mid
    Replies:
    4
    Views:
    7,278
    Scott Allen
    Dec 14, 2004
  3. Terry Reedy
    Replies:
    0
    Views:
    234
    Terry Reedy
    Jun 19, 2010
  4. Ken Yee
    Replies:
    2
    Views:
    199
    Ken Yee
    Oct 25, 2006
  5. techno

    Ado.Net SqlConnection in Classic ASP

    techno, Feb 28, 2010, in forum: ASP General
    Replies:
    2
    Views:
    1,389
    Bob Barrows
    Feb 28, 2010
Loading...

Share This Page