SQL Server Mentalist

Home | Pages | Archives

BISQL # 64 – How to Check for all running process and How to Kill running process

October 28, 2011 11:13 pm

Hi geeks ,

There is always we need to check for all running process and sometimes  Kill running process.

Also there are lot of cases where we need to kill all processes running for given database also for performing DDL and DML attribute.

In this article we are covering

  1. To Check for all running process.
  2. To find out which database associated with above dbid
  3. To find out Query associated with specific spid
  4. To kill this process

To Check for all running process :

Following script will helps us to find all running process associated with all database

Script 

USE
     Master
GO
SELECT
     SPID,DBID FROM SYSPROCESSES
WHERE 
     DBID NOT IN (1,2,3,4)-- 1 for Master, 2 for Tempdb, 3 for Mode, 4 for MSDB

AND  SPID > 50 --SQL Server reserves SPID values of 1 to 50 for internal use

AND  SPID <> @@spid  --To exclude current user process*/

Output

image

But What is this SPID and DBID ?

SPID – Process ID

DBID – Database ID

Now lets try to decode based on this SPID and DBID

To find out which database associated with above DBID :

Now lets find out what is this DB ID form above result

Script 

SELECT NAME
FROM   SYSDATABASES
WHERE  DBID=15 –Specify here above DBID

Output

image

To find out Query associated with specific spid

Now we got the database now I want to find actual Query associated with it !!

Script 

DECLARE @handle VARBINARY(64)
 
SELECT @handle = sql_handle
FROM   sys.sysprocesses
WHERE  SPID =57 -- Specify above SPID to get the query 
 
SELECT text
FROM  sys.dm_exec_sql_text(@handle)

Output

image

Analysis this query if you don’t think its going to be harm any of your process the we can actually

To kill this process

Now we just need to provide SPID to kill that process .

Script 

KILL 57 –Specify SPID to kill the process

Output

image

This is sequence how we are killing process with analysis of Database as well as query associated with it !!

Hope this explanation is useful for you !!

Thanks for visiting my blog !!

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

Stay Tuned :

Posted by Vishal Pawar

Categories: Query, SQL

Tags:

Leave a Reply

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.