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

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

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

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

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 !!