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
- To Check for all running process.
- To find out which database associated with above dbid
- To find out Query associated with specific spid
- To kill this process
To Check for all running process :
Following script will helps us to find all running process associated with all database
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*/
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
SELECT NAME FROM SYSDATABASES WHERE DBID=15 –Specify here above DBID
To find out Query associated with specific spid
Now we got the database now I want to find actual Query associated with it !!
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)
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 .
KILL 57 –Specify SPID to kill the process
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 !!