How To Terminate Database Session In MSSQL

As a basis/database administrator sometimes it is necessary to terminate database session in MSSQL.
When session is terminated, active transactions if any are rolled back, releasing the memory occupied by that session and it is then available for other database sessions.
In today’s topic we will describe various aspects of terminating sessions both for:

• Individual database session
• All sessions

First we will discuss how to identify sessions:

Identify which sessions are to be terminated

Before we can terminate database session in MSSQL, we need to identify them.
Using below query, we can find out the session details:

exec sp_who

Results are as below:

Here,

• spid – unique session ID
• status – process status
• loginname – login name used to identify application user.
• hostname – Denotes the hostname of Application user.
• blk – session ID of the blocking process (spid is blocked by blk)
• dbname – database name used by theprocess

Terminate database session in MSSQL:

For killing the individual sessions using SPID use below query:

Kill SPID

SPID can be found as shown in Fig 1

If we need to terminate ALL the sessions run below query with either sql server management studio or sql server command prompt

ALTER database [DATABASE-SID] SET SINGLE_USER with ROLLBACK IMMEDIATE;
Go

This will cause all incomplete transactions to be rolled back and any other connections to the MASTER database to be immediately disconnected.