SQL Script to kill problematic session from SQL Server

Leave a comment (0) Go to comments

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


SQL Script to kill problematic session from SQL Server sql server Identify Process ID sql server client running session process ID sql server all existing session processes ID SQL Script to kill problematic session from sql server

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

SQL Script to kill problematic session from SQL Server sql server Identify Process ID sql server client running session process ID sql server all existing session processes ID SQL Script to kill problematic session from sql server

EOF - SQL Script to kill problematic session from SQL Server, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.