Script to Get SQL Server Blocking Report

Script to Get SQL Server Blocking Report:

SELECT

CASE WHEN

CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0

THEN 'Waiting / Blocked'

ELSE 'Blocking' End AS 'Process_Type',

LTRIM(RTRIM(s.spid)) AS 'SPID',

LTRIM(RTRIM(s.blocked)) AS 'Blocked',

LTRIM(RTRIM(s.cpu)) AS 'CPU',

db_name(LTRIM(RTRIM(s.dbid))) AS 'DBName',

LTRIM(RTRIM(s.login_time)) AS 'Login_Time',

LTRIM(RTRIM(s.last_batch)) AS 'Last_Batch',

LTRIM(RTRIM(s.status)) AS 'Status',

LTRIM(RTRIM(s.loginame)) AS 'LoginName',

LTRIM(RTRIM(s.hostname)) AS 'HostName',

LTRIM(RTRIM(s.program_name)) AS 'ProgramName',

LTRIM(RTRIM(s.cmd)) AS 'CMD',

LTRIM(RTRIM(EST.TEXT)) AS 'Full_Query',

LTRIM(RTRIM(s.waittime)) AS 'Wait_Time',

LTRIM(RTRIM(s.lastwaittype)) AS 'Wait_Type',

LTRIM(RTRIM(s.waitresource)) AS 'Wait_Resource',

LTRIM(RTRIM(s.cpu)) AS 'CPU_Time(MS)',

LTRIM(RTRIM(s.physical_io)) AS 'Disk R/W',

LTRIM(RTRIM(s.memusage)) AS 'Mem_Usage(Total_Pages)',

LTRIM(RTRIM(s.open_tran)) AS 'NoOfOpenTran',

LTRIM(RTRIM(s.nt_domain)) AS 'Windows_Domain',

LTRIM(RTRIM(s.nt_username)) AS 'Windows_UserName'

FROM sys.sysprocesses s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)EST

WHERE spid in(select spid from sys.sysprocesses where blocked<>0) or

spid in(select blocked from sys.sysprocesses);

Script to Get SQL Server Blocking Report – Detailed:

SELECT

CASE WHEN

CAST( LTRIM(RTRIM(s.blocked)) AS SMALLINT) > 0

THEN 'Waiting / Blocked'

ELSE 'Blocking' End AS 'Process_Type',

LTRIM(RTRIM(s.spid)) AS 'SPID',

LTRIM(RTRIM(s.blocked)) AS 'Blocked',

db_name(LTRIM(RTRIM(s.dbid))) AS 'DBName',

LTRIM(RTRIM(s.login_time)) AS 'Login_Time',

LTRIM(RTRIM(s.last_batch)) AS 'Last_Batch',

LTRIM(RTRIM(s.status)) AS 'Status',

LTRIM(RTRIM(s.loginame)) AS 'LoginName',

LTRIM(RTRIM(s.hostname)) AS 'HostName',

LTRIM(RTRIM(s.program_name)) AS 'ProgramName',

LTRIM(RTRIM(s.cmd)) AS 'CMD',

ER.[statement_start_offset] AS 'Statement_Start',

ER.[statement_end_offset] AS 'Statement_End',

EST.TEXT AS 'Full_Query',

CASE

WHEN ER.[statement_start_offset] > 0 THEN

CASE ER.[statement_end_offset]  

   WHEN -1 THEN  

  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, 2147483647)

   ELSE  

  SUBSTRING(EST.TEXT, (ER.[statement_start_offset]/2) + 1, (ER.[statement_end_offset] - ER.[statement_start_offset])/2)  

END  

ELSE  

CASE ER.[statement_end_offset]  

   WHEN -1 THEN  

  RTRIM(LTRIM(EST.[text]))  

   ELSE  

  LEFT(EST.TEXT, (ER.[statement_end_offset]/2) +1)  

END  

END AS 'Exact_Statement',

LTRIM(RTRIM(s.waittime)) AS 'Wait_Time',

LTRIM(RTRIM(s.lastwaittype)) AS 'Wait_Type',

LTRIM(RTRIM(s.waitresource)) AS 'Wait_Resource',

LTRIM(RTRIM(s.cpu)) AS 'CPU_Time(MS)',

LTRIM(RTRIM(s.physical_io)) AS 'Disk R/W',

LTRIM(RTRIM(s.memusage)) AS 'Mem_Usage(Total_Pages)',

LTRIM(RTRIM(s.open_tran)) AS 'NoOfOpenTran',

LTRIM(RTRIM(s.nt_domain)) AS 'Windows_Domain',

LTRIM(RTRIM(s.nt_username)) AS 'Windows_UserName'

FROM sys.dm_exec_requests ER

CROSS APPLY sys.dm_exec_sql_text(ER.[sql_handle]) EST  

INNER JOIN sys.sysprocesses s ON ER.session_id = s.spid

WHERE ER.session_id IN

(SELECT spid FROM sys.sysprocesses WHERE blocked<>0

UNION

SELECT blocked FROM sys.sysprocesses)

ORDER BY ER.[session_id], ER.[request_id];