Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: