Tuesday, 7 January 2014

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 )