You are here:Home » tsql » Script to find blocking and Waiting processes in sql server

Script to find blocking and Waiting processes in sql server

-- TO FIND OUT WAITING PROCESS ------------

select ec.session_id,'dbcc inputbuffer ('+convert(varchar,ec.session_id)+')',st.text,
es.login_name,es.login_time,er.status,
er.start_time,datediff(minute,er.start_time,getdate()) as Diff_Min,er.command,er.database_id, db_name(er.database_id)as DBName,er.blocking_session_id,
er.wait_type,er.wait_time,er.last_wait_type,er.wait_resource,er.open_transaction_count,er.transaction_id,er.cpu_time,er.total_elapsed_time
from sys.dm_exec_connections ec
inner join sys.dm_exec_sessions es on (ec.session_id = es.session_id)
inner join sys.dm_exec_requests er on (ec.connection_id = er.connection_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
ORDER BY Diff_Min DESC



SELECT 'kill '+convert(varchar,spid),DB_NAME(dbid) AS DatabaseName, datediff(minute,last_batch,getdate()) as Diff_Min,'dbcc inputbuffer ('+convert(varchar,spid)+')' ,* FROM sys.sysprocesses
WHERE status <> 'background'
ORDER BY 3 DESC, cpu DESC



-- Blocking

SELECT * FROM sys.sysprocesses WHERE blocked <> 0




-- TO FIND NODE PROCESS FOR BLOCKING -----------

1.

SELECT spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,dbname=substring(db_name(dbid),1,10),cmd,
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0


--for 20005


2.

select r.session_id, r.blocking_session_id, r.database_id, r.command, s.last_request_start_time, s.login_name, r.last_wait_type, r.status
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
where (r.blocking_session_id > 0 and r.blocking_session_id <> r.session_id)
or r.session_id in (select session_id from sys.dm_exec_requests where blocking_session_id > 0 and blocking_session_id <> session_id)

--for 2000

1.

select spid, blocked, dbid, cmd, last_batch, loginame, lastwaittype, status
from master..sysprocesses
where (blocked > 0 and blocked <> spid)
or spid in (select spid from master..sysprocesses where blocked > 0 and blocked <> spid)