SQL blocking & Header Blocker

Problem

In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID's in SQL Server? Is there only one way to find out which spids are blocking? Are there commands I can run against multiple SQL Server versions? Check out this tip to learn more about locking and blocking.

Solution

Whenever a user contacts the DBA team indicating a process looks hung or a process is not proceeding, checking for database blocking makes a great deal of sense. Blocking happens when one database connection holds a lock and a second connection needs a lock on the same obect. This forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.

You can simple check the blocking by executing below command. 

Sp_who2 active

Below 2 scripts are useful when you are managing the large database environment and facing multiple blocking issues on databases. sometimes there are multiple blocking on database and you need to find out the header blocker and need that data with developer then below scripts are very useful.

--[Script 1] - To find the blocking****************************************

select a.spid,a.blocked,SUBSTRING(text, ( r.statement_start_offset / 2 ) + 1, 

( ( CASE WHEN r.statement_end_offset <= 0 

THEN DATALENGTH(text) 

ELSE r.statement_end_offset END - 

r .statement_start_offset ) / 2 ) + 1) AS statement_text,text, r.percent_complete,DATEDIFF (  

MINUTE , a.last_batch , GETDATE() ) [Min],DATEDIFF ( SECOND , a.last_batch , GETDATE() ) Sec  

,a.lastwaittype,r.start_time ,a.status,r.command,DB_NAME(a.dbid)  

DB,a.program_name,a.loginame,a.hostname,b.client_net_address Client_IP,b.client_tcp_port  

Client_Port,b.local_net_address Local_IP,b.local_tcp_port 

Local_Port,a.cpu,a.physical_io,a.memusage,a.waittime

--,a.last_batch,query_plan 

from

sys.sysprocesses a left join sys.dm_exec_connections b on(a.spid =b.session_id) left join 

sys.dm_exec_requests r on (a.spid=r.session_id) and (b.connection_id =r.connection_id )  

cross

apply sys.dm_exec_sql_text(r.sql_handle) 

cross

apply sys.dm_exec_query_plan(r.plan_handle) 

where

a.status not in('sleeping','background') and 

a.spid<>@@spid order by a.cpu desc

--[Script 2] To find the Header blocker****************************************

  select hostname,blocked, * from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0

and blocked not in (select spid from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0) order by waittime desc

select hostname, blocked,* from master.. sysprocesses with(nolock) where spid in

(select blocked from master.. sysprocesses with(nolock) where  spid<>blocked and blocked<>0 and blocked not in 

(select spid from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0))

[Note] - You can combinedly run both the scripts to find out the blocking sessions and header blocker at the same time