SQL command / script to exclude/terminate/kill a problematic sessions from a SQL server.
-- SQL Script to kill problematic session KILL 54 WITH STATUSONLY;
This script can be used if you wanted to kill a particular process from the server. But prior to kill a particular session / process from the server, we need to identify the process ID/ Session ID.
How to Identify the Process ID ?
Process ID can we find either from server or from client. From server, we can list all processes but from client we can see only client current/last process.
How can a client identify it’s current running session / process ID?
Client can view it’s current session ID from the SSMS (SQl Server Management Studio only), When you connect to SQL Server using SSMS, we get a status bar, which gives the information about
- Process Status
- Current Login ID
- Session ID
- Current Database name
- Time elapsed in last executed statement
- Number of rows returned by last query
Screen shot to see the current process ID / Session ID
How DBA can view all existing session / processes ID from the SQL Server?
DBA can view it’s current session ID / process fro sysprocess table. following script can be used to view all current session ID’s on a connected SQL instance.
-- Script to view all currently processes / sessions on the server select * from master.dbo.sysprocesses
OUTPUT
