Old processes in both sp_who2 and sysprocesses

  • Hi,

    We're having a big trouble to determine what could be causing this, and I hope someone can help me.

    One of our clients noticed that there are lots of results when executing sp_who2 or a select in sysprocesses for both databases of our system. The problem here is that a good portion of those is from dates before the actual day (both login_time and last_batch, which are almost the same), and there are processes there with more than 10 days...

    The process named as the caller is one of our components, but as far as we could find, it's always closing the connection. It seems that SQL isn't knowing what to do with those connections.

    Could somebody help me with which should I do to find out what could be wrong? We are not getting the same trouble in our development environment. If more info is needed, please let me know.

    Thanks in advance

    Marcelo

  • I would be looking harder at the application.  I have never experienced an issue with a SQL thinking that a connection is open if the application has closed it properly. 

    SQL will keep a connection open forever (i.e. until it is restarted or you use the KILL command on the connection) if the application does not close it.

  • What's the connection method from the APP ?

    If using MDAC, is MDAC on same level ?

    If using JDBC, are the up to the jdbc sp1 ?

     

     

  • Hi,

    We use ADO to connect to the database, and yes, we use MDAC, but what do you mean by same level? Is there any other info I can provide that would be of help?

    Strange enough is that we can't reproduce this behaviour in our labs.

    Thanks

    Marcelo

  • Believed it or not, the MDAC is the enemy in a lot of cases that I had deal with. Make sure your server and client are on the latest MDAC level. I beleive MDAC 2.7 is kind of buggy. If possible you should apply the latest MDAC 2.7 SP and patches to both your SQL server and client machine and see your problem go away.

     

  • I'll be checking this and will post here what I find out... I would like to reproduce this behaviour here, maybe I'll try that...

    Thanks!

    Marcelo

  • Your issue may be related with orphaned sessions. An orphaned session occurs when the client terminates suddenly (i.e. power failure on the client, client computer is physically shut down) without having a chance to free the network connections it is holding. In these cases SQL Server continues to keep resources owned by the client until they are killed.

    To find out if you have orphaned sessions use the stored procedure sp_who to report information on sessions. Orphaned sessions can be identified if the status of a process is awaiting command and the interval of time found by subtracting last_batch from GETDATE() is longer than usual for the process.

    If the session host name is (i.e. client computer) is down, it is definitely orphaned.

    To close an orphaned SQL Server session, use the KILL statement. All resources held by the session are then released.

    Note: if your application crashes or you terminate its process from Task Manager it is cleaned up immediately by Windows NT and very seldom will result in an orphaned session.

    Review “Orphaned Sessions” in BOL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply