Scripts to identify Blocking in SQL Server

Find below different way of identifying blocking in SQL Server.

Option -1

SP_WHO2

Option – 2

SP_WHO2

Mention the specific SPID below.

DBCC INPUTBUFFER(<SPID>)

Ex. DBCC INPUTBUFFER(54)

Option – 3

DECLARE @sql_handle VARBINARY(64)

SELECT @sql_handle = sql_handle FROM sys.dm_exec_requests WHERE session_id=54 and request_id = 0

SELECT dbid, objectid, text FROM sys.fn_get_sql(@sql_handle)

Option – 4

Detailed information can be found from below script.

SELECT

                                    p.spid

                                    ,convert(char(12), d.name) db_name

                                    , program_name

                                    , convert(char(12), l.name) login_name

                                    , convert(char(12), hostname) hostname

                                    , cmd

                                    , p.status

                                    , p.blocked

                                    , login_time

                                    , last_batch

                                    , p.spid

FROM     

                                    master..sysprocesses p

                                    INNER JOIN master..sysdatabases d ON p.dbid =  d.dbid

                                    INNER JOIN master..syslogins l ON p.sid = l.sid

WHERE    

                                    p.blocked = 0

                                    AND   EXISTS ( SELECT 1 FROM  master..sysprocesses p2 WHERE p2.blocked = p.spid )