Sql 2005 sp_who Status - Which ones can I kill?

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

QuestionManAAuthor Commented:
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?
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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.