Sql 2005 sp_who Status - Which ones can I kill?

Posted on 2008-02-11
Medium Priority
Last Modified: 2010-05-18
Good Day,

sp_who results in several records with a status of "sleeping". Is it safe to kill those and if so can I terminate them by status not spid?

Question by:QuestionManA
  • 2

Expert Comment

ID: 20868121
Why do you wish to kill off the connections? Sleeping is representing that they are not doing anything, but could still be connected.

Anything with a SPID below 50 is a system task and killing them would definately be unadvised, kill connections by Id only, and then only if you really have to. Before you do kill anything run sp_who2 so you at least get an idea of which programs are attached (program name column) - before arbitarily killing a connection.


Author Comment

ID: 20868708
Thank you for your help.

We're having a tough time finding any abandoned connections in our app and from time to time we receive an error about reaching our max connections the the pool.

I was hoping that I could schedule a sp to kill any sleeping connections.
The majority of them (of spid > 50) are: .Net SqlClient Data Provider                          

What are your thoughts?

Accepted Solution

drydenhogg earned 200 total points
ID: 20874431
You can kill them but I would expect the application to react badly to having the connections killed like that, it depends on the manner in which it was written as to how resiliant it is to dropped connections. Given that the code is not closing it's own connections properly, it might not be very resiliant. the best solution, but perhaps the one you can't get done is that the dev's tidy their connections.

If you wish to reduce how long a sleeping connection is held open, in the server configuration manager, expand the SQL Server 2005 Network configuration, click on the protocols and the list of available protocols will be displayed.

Assuming you are using TCP/IP which is pretty common bring upt he properties for TCP/IP and there is a KeepAlive setting, which measures in milliseconds, how long a dormant tcp/ip connection is maintained for within SQL. This may get rid of some of the connections that are doing nothing, it depends on whether the machine that made it is still on and responding to the message.

If you alter this to a lower value, re-test the application to make sure no impact occurs.



Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

601 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