How do I kill a session in SQL Server, not kill a process?
Well, let's imagine that you are having a problem with some users' connections from time to time and that it's generating orphaned sessions*. We need to kill that session because it is taking some resources from our server and a license, too. You must use the KILL command which is commonly used to kill processes, but let's not forget that a session appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense.**
First you will need to execute the sp_who stored procedure. A table will appear with the columns: spid, status and loginame. The orphaned session can be identified with the text "awaiting command" under the status column. After that, you will execute the KILL command: KILL spid.
* An orphaned session is a session that remains open on the server side after the client has disconnected. It could occur when the client is unable to free network connections or when a client computer loses power unexpectedly or was turned off without a proper shutdown.
** Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
This was first published in April 2001