Q

How do I kill a session in SQL Server, not kill a process?

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

This was first published in April 2001

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close