SQL Server Clients Fail to Disconnect

Posted on 2007-10-04
Last Modified: 2010-03-19

Running SQL2005, SP2.  Checking connection status using Management | View Processes.

Users show connected long after they disconnect, even after they completely shut down their computer.  

I use refresh to refresh the Process View, but those connections are actually still there.

I can kill them, and then they're gone.  But why are they hanging on after the client has clearly disconnected?

Any theories?
Question by:gateguard
    LVL 18

    Accepted Solution

    Yep, when they do not 'sign off' nicely, but just terminate their connection, SQL Server will keep the spid alive for a certain amount of time. So the problem is with the app they are using. Make sure the connection object is nicely destroyed in the code and this won't happen anymore.

    ... I assume that there is a client-server app here. When you are using a app-app server-sql server architecture, then it's a whole different story. Then you might be facing a connection pool ... but reading your Q, I would say it's the client-SQL Server architecture here ...

    Hope this helps ...
    LVL 6

    Assisted Solution

    Specific to my situation -- but may help you in the right direction -- I recently had same problem with one user -- tracked it back to his Ethernet cable was stretched to the max so that he occasionally got disconnected.  Recommended he replace his cord with a retractable one.

    Is it a connection from SSMS or another app?

    Author Comment

    It's a connection from an application.  Interesting ideas.  I'm going to experiment with re-creating this myself.
    LVL 18

    Expert Comment

    Glad I could be of any help and thanks for the grade !

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now