Blocking full details in sql server 2008 and above

The blocking details in SQL server 2008 and above.

  In a SQL server there might be multiple blocking. Meaning one session might be blocking another session.Then we need to find the root blocking and kill if necessary. The below script will find the root blocking and one of the blocked session by that root blocking.If the server has multiple 
root blocking also , the script will give the output. I strongly recommend not use the second script.

Script 1: this script will find the root blocking and give the complete details about it.

you can also download the script 1 from the below link

https://gallery.technet.microsoft.com/ROOT-BLOCKING-IN-SQL-SERVER-839cfe81

create table ##temptab(blk int)

insert into ##temptab (blk) select blocked from sys.sysprocesses where blocked not in 

(select spid from sys.sysprocesses where blocked <>0) and  blocked<>0

declare @blc int

declare cur cursor for select distinct blk from ##temptab

OPEN cur   

FETCH NEXT FROM cur INTO @blc

WHILE @@FETCH_STATUS = 0   

BEGIN  

select  x.SESsION_ID AS [ROOT BLOCKING],x.host_name[blocking host],

x.login_name [blocking login],y.*,x.text [blocking text] from (SELECT es.*,

st.text FROM sys.dm_exec_sessions es

inner join sys.dm_exec_connections ec on ec.session_id=es.session_id

cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

where es.session_id=@blc)

 x ,(select spid [blocked],blocked [blocking],status [blocked status],

loginame[blocked login],hostname [blocked host],waittime/60000 [in min],

text [blocked text] from sys.sysprocesses r 

cross apply sys.dm_exec_sql_text(sql_handle) st

where r.blocked=@blc) y

FETCH NEXT FROM cur INTO @blc

END   

CLOSE cur  

DEALLOCATE cur

drop table ##temptab

******************************************************************************************************************************************************************

Script 2: this will create a procedure to find the root blocking and their details .

               let us consider that you created the procedure in master database, then you need to run the 

               script like 'master..block'

create procedure [dbo].[block]

as

create table ##temptab(blk int)

insert into ##temptab (blk) select blocked from sys.sysprocesses where blocked not in 

(select spid from sys.sysprocesses where blocked <>0) and  blocked<>0

declare @blc int

declare cur cursor for select distinct blk from ##temptab

OPEN cur   

FETCH NEXT FROM cur INTO @blc

WHILE @@FETCH_STATUS = 0   

BEGIN  

select  x.SESsION_ID AS [ROOT BLOCKING],x.host_name[blocking host],

x.login_name [blocking login],y.*,x.text [blocking text] from (SELECT es.*,

st.text FROM sys.dm_exec_sessions es

inner join sys.dm_exec_connections ec on ec.session_id=es.session_id

cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) st

where es.session_id=@blc)

 x ,(select spid [blocked],blocked [blocking],status [blocked status],

loginame[blocked login],hostname [blocked host],waittime/60000 [in min],

text [blocked text] from sys.sysprocesses r 

cross apply sys.dm_exec_sql_text(sql_handle) st

where r.blocked=@blc) y

FETCH NEXT FROM cur INTO @blc

END   

CLOSE cur  

DEALLOCATE cur

drop table ##temptab

GO

******************************************************************************************************************************************************************