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:
Results are as below:
- 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:
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.